split column in 2
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: split column in 2

  1. #1
    Join Date
    Apr 2001
    Location
    London
    Posts
    725
    Hi Friends..

    I have a column much like this one :-

    company_name
    --------------------
    joe bloggs T/A a company name
    joe bloggs the 2nd T/A another company name

    I need the sql that would split this column so that it looks like this through sqlplus :-

    company_name trading_as
    ------------------ -----------------------
    joe bloggs a company name
    joe bloggs the 2nd another company name

    Any help with this would be much apreciated.

    Thanks

    Suresh

    Once you have eliminated all of the impossible,
    whatever remains however improbable,
    must be true.

  2. #2
    Join Date
    Oct 2000
    Location
    Germany
    Posts
    1,185
    Use the instr, substr, and strlen functions.

    Find the string T/A with instr.

    Then substr from 1 to the beginning of T/A to get the first column

    Then substr from T/A to the string length to get the second column.

    Good luck.
    David Knight
    OCP DBA 8i, 9i, 10g

  3. #3
    Join Date
    Apr 2001
    Location
    London
    Posts
    725
    Hi..

    Does this mean that I'll have to do this through a pl/sql block ?

    Is there not a way using just sql ?

    Once you have eliminated all of the impossible,
    whatever remains however improbable,
    must be true.

  4. #4
    Join Date
    Oct 2000
    Location
    Germany
    Posts
    1,185
    It is just SQL*Plus.

    Do you have documentation? If not, i can post some sample code.
    David Knight
    OCP DBA 8i, 9i, 10g

  5. #5
    Join Date
    Oct 2000
    Location
    Germany
    Posts
    1,185
    This is quick and dirty (not tested), but the basic idea is okay.

    select substr(company_name, 1, instr(company_name, 'T/A'),
    substr(instr(company_name, 'T/A') + 2, strlen(company_name))
    from table_name;

    Hope that helps.
    David Knight
    OCP DBA 8i, 9i, 10g

  6. #6
    Join Date
    Apr 2001
    Location
    London
    Posts
    725
    Hi David..

    you're the daddy !!

    modified it a little bit but works like a dream!!!

    Thanks for your help

    Suresh
    Once you have eliminated all of the impossible,
    whatever remains however improbable,
    must be true.

  7. #7
    Join Date
    May 2001
    Posts
    55
    Honestly Suresh you need not work so hard.

    This will do

    select replace (COMPANY_NAME_TRADING_AS ,'T/A','')
    from COMPANY
    A farmer learns more from a bad harvest than a good one

  8. #8
    Join Date
    Oct 2000
    Location
    Germany
    Posts
    1,185
    The output will be one column and not two.
    David Knight
    OCP DBA 8i, 9i, 10g

  9. #9
    Join Date
    May 2001
    Posts
    55
    Oh! Sorry, I was a bit too quick in answering that one!

    Suresh's example output (copied below) gave me a wrong impression that the output was needed in one column.

    ----

    need the sql that would split this column so that it looks like this through sqlplus :-

    company_name trading_as
    ------------------ -----------------------
    joe bloggs a company name
    joe bloggs the 2nd another company name

    ----
    A farmer learns more from a bad harvest than a good one

  10. #10
    Join Date
    Apr 2001
    Location
    London
    Posts
    725
    Hi David..

    one point..

    I have never heard of or able to use strlen function

    I used length(column_name) instead.

    is strlen a function ?

    I have sqlplus r8.1.7


    Once you have eliminated all of the impossible,
    whatever remains however improbable,
    must be true.

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