How do I replace a value with another value in sql loader?

Question

I want to replace the values that will be loaded to the domain_label column as following:

if 3 cntg03 replace with cntg03

if 2 cntg02 replace with cntg02

if 1 cntg01 replace with cntg01

Is this possible?

I have the following control file:

LOAD DATA 
INFILE u/local/r2x/data/rda_prod_cntg.dat
TRUNCATE 
INTO TABLE R2X1.RDA_PROD_CNTG
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '\"'
(
ITEM, 
GLOBAL_DOMAIN_TYPE CONSTANT 'S',
GLOBAL_ID CONSTANT 10,
GLOBAL_DOMAIN_DESC CONSTANT 'Sales Forecast',
filler2 FILLER,
filler3 FILLER,
filler4 FILLER,
filler5 FILLER,
filler6 FILLER,
filler7 FILLER,
filler8 FILLER,
filler9 FILLER,
filler10 FILLER,
filler11 FILLER,
filler12 FILLER,
filler13 FILLER,
filler14 FILLER,
filler15 FILLER,
filler16 FILLER,
filler17 FILLER,
filler18 FILLER,
filler19 FILLER,
filler20 FILLER,
DOMAIN_ID,
DOMAIN_LABEL,
filler23 FILLER,
filler24 FILLER,
filler25 FILLER, 
filler26 FILLER 
)

Answer 1

You can use a case expression for that column.

DOMAIN_LABEL "case when :DOMAIN_LABEL = '3 cntg03' then 'cntg03'
                   when :DOMAIN_LABEL = '2 cntg02' then 'cntg02'
                   when :DOMAIN_LABEL = '1 cntg01' then 'cntg01'
              end"

If this has to be done for multiple values and if the pattern is similar you can use the substr function and get all the characters starting from the 3rd character,

DOMAIN_LABEL "substr(:DOMAIN_LABEL,3)"

The full script would be

LOAD DATA 
INFILE u/local/r2x/data/rda_prod_cntg.dat
TRUNCATE 
INTO TABLE R2X1.RDA_PROD_CNTG
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '\"'
(
ITEM, 
GLOBAL_DOMAIN_TYPE CONSTANT 'S',
GLOBAL_ID CONSTANT 10,
GLOBAL_DOMAIN_DESC CONSTANT 'Sales Forecast',
filler2 FILLER,
filler3 FILLER,
filler4 FILLER,
filler5 FILLER,
filler6 FILLER,
filler7 FILLER,
filler8 FILLER,
filler9 FILLER,
filler10 FILLER,
filler11 FILLER,
filler12 FILLER,
filler13 FILLER,
filler14 FILLER,
filler15 FILLER,
filler16 FILLER,
filler17 FILLER,
filler18 FILLER,
filler19 FILLER,
filler20 FILLER,
DOMAIN_ID,
DOMAIN_LABEL "substr(:DOMAIN_LABEL,3)",
/* or you can use a case expression
 DOMAIN_LABEL "case when :DOMAIN_LABEL = '3 cntg03' then 'cntg03'
                    when :DOMAIN_LABEL = '2 cntg02' then 'cntg02'
                    when :DOMAIN_LABEL = '1 cntg01' then 'cntg01'
               end",
*/

filler23 FILLER,
filler24 FILLER,
filler25 FILLER, 
filler26 FILLER 
)