Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2004
    Posts
    93

    Talking Unanswered: stipping out carraige returns in a single column

    I trying to strip out a column that has carraige returns so I can break it into 4 columns.

    for example it reads

    Address:
    84 Hamilton Road||Millhouse||Millane|Uk

    so that I can have

    col1 col2 col2 col4
    address1, address2, address3, address4
    84 Hamilton Road Millhouse Millane UK

    I will used decode so that if address3 or addresss4 is blank then have a null value. I have tried to use Decode(Substring(instring

    any ideas????
    BTW..i have looked on GOOGLE and I have no help...

    Cheers
    E
    Cheers
    Etravels

  2. #2
    Join Date
    Jan 2004
    Location
    Issy les Moulineaux, France
    Posts
    24
    You can use the following query :
    Code:
    select decode(c1,0,c,substr(address,1,c1-1)) address1,
    decode(c1,0,null,decode(c2,0,substr(address,c1+1),substr(address,c1+1,c2-c1))) address2,
    decode(c2,0,null,decode(c3,0,substr(address,c2+1),substr(address,c2+1,c3-c2))) address3,
    decode(c3,0,null,substr(address,c3+1)) address4
    from (select
           instr(address,'
    ',1,1) c1,
           instr(address,'
    ',1,2) c2,
           instr(address,'
    ',1,3) c3, c
     from mytable )
    Eric.

  3. #3
    Join Date
    Feb 2004
    Posts
    93

    thanx for that

    Thanx a million, that really helped me

    Cheers
    Cheers
    Etravels

Posting Permissions

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