trimming white space & blanks
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: trimming white space & blanks

  1. #1
    Join Date
    Feb 2001
    Posts
    75
    Hi,

    I am loading data through sqlldr direct=true. My data fileds are terminated by some control character like '^^'. I want both leading & trailing blanks removed in character fields.

    Is it possible through control file? If yes, how? By default, sqlldr does not do.

    kailash pareek

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Is it always the same character? If so, find the hex value for that char and you can use it as a delimiter in your .ctl file. You would use something like:
    fields terminated by x'12' ...
    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
    May 2000
    Location
    Portsmouth, NH, USA
    Posts
    378

    Talking

    i believe you can use the "WHEN" parameter to specify what characters you want ignored.

    Any administration book should have info on this.

    - Magnus

  4. #4
    Join Date
    Feb 2001
    Posts
    75

    Trimming of blanks in sqlldr

    Hi,

    Thanks for your reply, but that is not what I want. I am using terminated by clause. My question is a value ^^ aaa ^^ is being loaded as one having two blanks on either side.

    I want to trim from both sides and can not used functions as I am using direct=true, neither I want to do that by some post load processing logic.

    Is there is sqlloader itself something which I am missing?

    Kailash pareek

  5. #5
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    You can apply the rtrim(ltrim()) function within your sqlldr control file.
    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."

  6. #6
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Originally posted by marist89
    You can apply the rtrim(ltrim()) function within your sqlldr control file.
    For example:
    fields terminated by x'12' optionally enclosed by x'15' (
    field1 "rtrim(ltrim(:field1))",
    ...
    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."

  7. #7
    Join Date
    Feb 2001
    Posts
    75

    Trimming of blanks in sqlldr

    Hi,

    Thanks for ltrim/rtrim. My data is millions of rows and I am using DIRECT=TRUE. The sql operators are not allowed in direct mode.

    Any more suggestions ?

    kailash pareek

  8. #8
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Don't use direct=true
    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