Substring Function in SQL Loader

[edsperez]

Hi,

Is it possible to use substring function in a SQL Loader control file?

Thanks,

Ed

[marist89]

Sure, you can. Your controlfile entry would look something like:
field1 "substr(:field1,1,10)",
field2 char(20),
.....

[edsperez]

I'm having trouble figuring it out, How do I create the control file when the next column data is also found in the first column of the comma delimited file.

Using this example, how will the control file look like.


Thanks,

Ed

--City_State Table
City varchar2(9)
St varchar2(2)
Ctry varchar2(3)

--Data(comma delimited)
GREENVILLSC,USA
STPAUL MN,USA
CHICAGO IL,USA
FREMONT NE,USA

--control file
----FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(City "substr(:City, 1,9)",
St "substr(:City,10,11)",
Ctry
)

[sambavan]

[CODE]
(City "substr(:City, 0,INSTR(:City,' ',1))",
St "substr(:City,INSTR(:City,' ',1)+1)",
Ctry
)

[code]

On this case the string before the first blank would be stored as the city and the rest from the first space would be stroed as state.

Sam

[marist89]

I bow to the master!