-
Hi,
Is it possible to use substring function in a SQL Loader control file?
Thanks,
Ed
-
Sure, you can. Your controlfile entry would look something like:
field1 "substr(:field1,1,10)",
field2 char(20),
.....
Jeff Hunter
-
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
)
-
[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
Thanx
Sam
Life is a journey, not a destination!
-
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|