使用SQL*Loader加载数据时，如何移除输入文件中的负号并将-747585959加载为747585959？

Got it, let's sort out how to strip that leading minus sign from your first column during SQL*Loader processing. Since every row's first column has a - prefix, we can handle this directly in your control file with a couple of straightforward approaches:

Method 1: Use the REPLACE SQL Function #

This is the most flexible option, especially if your input fields are delimited (like comma-separated). In your control file, define the column with a SQL expression that replaces the hyphen with an empty string:

LOAD DATA
INFILE 'your_input_data.txt' -- Replace with your actual input file path
INTO TABLE your_target_table -- Replace with your target table name
FIELDS TERMINATED BY ',' -- Adjust this to match your file's delimiter (e.g., '|', whitespace)
TRAILING NULLCOLS
(
    target_column INTEGER "REPLACE(:target_column, '-', '')"
)

• :target_column references the raw value being loaded from the input file
• The REPLACE function removes the - character, and we cast the result to your desired numeric type (like INTEGER or NUMBER)

Method 2: Use SUBSTR for Fixed-Length Fields #

If your first column is a fixed-length field (e.g., every value is exactly 10 characters long, starting with -), you can just extract the substring starting from the second character:

LOAD DATA
INFILE 'your_input_data.txt'
INTO TABLE your_target_table
FIELDS FIXED
(
    target_column INTEGER "SUBSTR(:target_column, 2)" -- Grab everything after the first character
)

This works perfectly when you know the minus sign is always the first character and the rest is pure numeric data.

Quick Notes #

• Make sure your target table's column is defined to accept positive numbers (no need for a signed numeric type unless you might have positive values later)
• Test with a small subset of your data first to confirm the transformation works as expected
• If your input uses a different format (like enclosed in quotes), adjust the control file's field definitions accordingly (e.g., OPTIONALLY ENCLOSED BY '"')

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