Substring Function in SQL Loader
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Substring Function in SQL Loader

  1. #1
    Join Date
    Jan 2001
    Location
    St. Louis. MO
    Posts
    150

    Cool

    Hi,

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

    Thanks,

    Ed

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Sure, you can. Your controlfile entry would look something like:
    field1 "substr(:field1,1,10)",
    field2 char(20),
    .....
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    Join Date
    Jan 2001
    Location
    St. Louis. MO
    Posts
    150
    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
    )



  4. #4
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    [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!


  5. #5
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    I bow to the master!
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width