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)) 

Answers


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)"
)

Need Your Help

windows.onbeforeunload - Is there a way of calling a function before leave/stay?

javascript html onbeforeunload

I'm trying to ask the use if he really wants to leave the page where a youtube video is playing. But before asking the question "Are you sure to leave the page ?", I want to pause the video.

How do I make a web app that also works locally?

web-applications privacy

I'm thinking of building a web app that helps people analyse their investments. Users would upload their mutual fund statement, and the tool would analyse it and give them some conclusions.