Oracle表中批量修正Excel导入错误数据：移除末尾E9及小数点

Looks like you're dealing with Excel's automatic scientific notation conversion messing up your Oracle table data—super common issue when importing large numbers! Let's walk through how to clean up those values (like 8.632233817E9) by removing the decimal point and trailing E9, while keeping any 9s in the middle of the number intact.

Step 1: Verify Cleaned Values First #

Before updating anything, always validate the transformation works as expected with a SELECT query. We'll use Oracle's string functions to strip out the unwanted parts:

Option 1: Basic String Functions (No Regex)

Perfect if all your problematic values end exactly with E9:

SELECT
    RRSOC_ID,
    STORE_SITENAME_LANDL_2 AS original_value,
    -- First trim the trailing 'E9', then remove the decimal point
    REPLACE(SUBSTR(STORE_SITENAME_LANDL_2, 1, LENGTH(STORE_SITENAME_LANDL_2) - 2), '.', '') AS cleaned_value
FROM TBL_RRSOC_STORE_INFO
-- Filter only rows with the scientific notation issue
WHERE STORE_SITENAME_LANDL_2 LIKE '%E9';

Option 2: Regular Expressions (More Flexible)

Handy if you anticipate minor variations (though you specified E9):

SELECT
    RRSOC_ID,
    STORE_SITENAME_LANDL_2 AS original_value,
    -- Replace both decimal points and trailing 'E9' with empty string
    REGEXP_REPLACE(STORE_SITENAME_LANDL_2, '\.|\E9$', '') AS cleaned_value
FROM TBL_RRSOC_STORE_INFO
WHERE REGEXP_LIKE(STORE_SITENAME_LANDL_2, 'E9$');

Step 2: Update the Table #

Once you confirm the cleaned_value matches your requirements, run the UPDATE statement to fix the data:

Option 1: Basic String Functions

UPDATE TBL_RRSOC_STORE_INFO
SET STORE_SITENAME_LANDL_2 = REPLACE(SUBSTR(STORE_SITENAME_LANDL_2, 1, LENGTH(STORE_SITENAME_LANDL_2) - 2), '.', '')
WHERE STORE_SITENAME_LANDL_2 LIKE '%E9';

-- Commit the changes if auto-commit is disabled
COMMIT;

Option 2: Regular Expressions

UPDATE TBL_RRSOC_STORE_INFO
SET STORE_SITENAME_LANDL_2 = REGEXP_REPLACE(STORE_SITENAME_LANDL_2, '\.|\E9$', '')
WHERE REGEXP_LIKE(STORE_SITENAME_LANDL_2, 'E9$');

COMMIT;

Key Tips #

• Backup First: Always create a backup of the table (or affected rows) before updates. Use CREATE TABLE TBL_RRSOC_STORE_INFO_BACKUP AS SELECT * FROM TBL_RRSOC_STORE_INFO; to save a copy.
• Primary Key Safety: Since RRSOC_ID is your primary key, you can be confident each row is unique and you won’t accidentally update duplicate records incorrectly.
• Prevent Future Issues: Next time you import from Excel, format the target column as "Text" before exporting/importing to stop Excel from converting large numbers to scientific notation.

内容的提问来源于stack exchange，提问作者Nadeem