CODEWITHSUNDEEP
oracle-databasesql-loader
flyingbird013
flyingbird013

Reputation: 476

Oracle sqlldr : remove delimiter

I have a NPWP column that contains the numbers shown below :

npwp

012345678-613.001
001234565-005.000

and npwp's table

create table npwp (
no_npwp varchar2(15)
)

how to tell loader.ctl in order to remove the mark - and . ?

like that ? 

OPTIONS (SKIP=11, errors=12000) 
LOAD DATA  
APPEND INTO TABLE npwp (
npwp POSITION(1:9)||POSITION(11:3)||POSITION(15:3))

Upvotes: 0

Views: 601

Answers (1)

user1645975
user1645975

Reputation: 101

Use sql expressions. For instance:

APPEND INTO TABLE npwp (
  npwp "REPLACE(REPLACE(:npwp ,'-'),'.')"
)

or

APPEND INTO TABLE npwp (
  npwp "substr(:npwp, 1, 9) || substr(:npwp, 11, 3) || substr(:npwp, 15)"
)

Upvotes: 1

Related Questions