Break one column to multiple columns
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Break one column to multiple columns

Hybrid View

  1. #1
    Join Date
    Feb 2001
    Posts
    203
    Hi Guys,

    In one column i have a values like this.
    Full Name
    ---------------
    "Keith Warn"
    "Hary Peter"
    "Mag B Boston"

    I want to break that column as 2 columns. like this..
    First_column second column Third_column
    ----------------- ----------------- -----------------
    Keith Warn
    Hary Peter
    Mag b Boston

    Any ideas? Thanks.

    Sree.
    sree

  2. #2
    Join Date
    Aug 2001
    Location
    chennai,bangalore
    Posts
    840
    U can do only for reporting purpose using query like
    select column1 col1,column1 col2 from table_name;

    U cannot break or split a column.Since there are attributes of an entity you need to create them explicitly...

    regards
    anandkl

    [Edited by anandkl on 07-12-2002 at 06:32 PM]
    anandkl

  3. #3
    Join Date
    Feb 2001
    Posts
    203
    Thanks for fast respone.

    For example if we take INITCAP command it will make first letter capitale to all words.

    For example.

    SELECT INITCAP('the soap') "Capitals" FROM DUAL;

    Capitals
    ---------
    The Soap

    "INITCAP returns char, with the first letter of each word in uppercase, all other letters in lowercase. Words are delimited by white space or characters that are not alphanumeric. "

    So oracle is recognizing the difference between words in a column by delimiting the spaces.

    But i don't know why oracle is not provided any function for this.

    Any body have other ideas on this. Any suggestion will help me. Thanks.

    Sree.
    sree

  4. #4
    Join Date
    May 2002
    Posts
    2,645
    But, what you can do is select a substring out of the column, which is hokey because of where the break in the name occurs, OR use PL/SQL to select each row into a cursor, then compare each character in the name string to an array - once you hit a space, take what's been selected out into a concatenated name string and do an insert into a new, properly formed table. From there, take the remaining characters in the array, minus the space, concatenate those together, and do the second insert. Sounds like a pain in the butt? Yes. Should have created the table correctly in the first place, but all may not be lost. How was the data put in the table? One line at a time by hand? Or with a script? If with a script, modify it accordingly. If by hand, live and learn.

  5. #5
    Join Date
    Feb 2001
    Posts
    203
    Hi Stecal,

    Ha...Ha.. Good Suggestion. I agree with you. Should have table created correctly.

    This is a old data which is entered by users long time back.
    Now we want to change that field from one to 3 fields.

    But thanks for spending your time on this.

    Hi Guys, Is there another way. Any suggestions welcome.

    Sree.
    sree

  6. #6
    Join Date
    May 2002
    Posts
    2,645
    Okay, another way: select * from bad_table into a flat file - save it out to an external file that is tab delimited. Edit the file as necessary, then use SQL Loader to read in the flat file. How's that work for you?

  7. #7
    Join Date
    Feb 2001
    Posts
    203
    30 columns and 6,50,000 records. Any other idea's.

    Sree.
    sree

  8. #8
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    As I can understand, you want to break the contents of your COL_X into three different columns COL_1 to COL_3 undeer the following rules:

    COL_1: everything from first character up to the first blank character
    COL_2: everything from the first blank to the second blank
    COL_3: everything following the second blank

    So here is somewhat messy code, but it should work:
    Code:
    SELECT SUBSTR(col_x, 1, DECODE(INSTR(col_x, ' ', 1, 1), 
                                       0, LENGTH(col_x),
                                       INSTR(col_x, ' ', 1, 1)-1)
                                      ) AS col_1,
           SUBSTR(col_x, DECODE(INSTR(col_x, ' ', 1, 1), 
                                       0, LENGTH(col_x)+1,
                                       INSTR(col_x, ' ', 1, 1)+1),
                             DECODE(INSTR(col_x, ' ', 1, 2), 
                                       0, LENGTH(col_x),
                                       INSTR(col_x,' ', 1, 2)-1)
                                      ) AS col_2,
           SUBSTR(col_x, DECODE(INSTR(col_x, ' ', 1, 2), 
                                       0, LENGTH(col_x)+1,
                                       INSTR(col_x, ' ', 1, 2)+1)) AS col_3
    FROM my_table;
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  9. #9
    Join Date
    Feb 2001
    Posts
    203
    Hi Jmodic,

    Really you guys are great. Your solution is great but still it's not working completly.

    In the table i have values like this

    harry peter
    mag b boston
    Keith warn

    your query output is

    COL_1 COL_2 COL_3
    -------------------- -------------------- --------------------
    harry peter
    mag b bos boston
    Keith warn

    That bos is repeting. And if i have another value
    like
    "ben j sam jr" then i want to break into 4 columns.

    Sorry, I know that i am giving trouble to you guys. But this is a big problem to me. So please don't take me bad.

    Thanks Jmodic and stecal.
    sree

  10. #10
    Join Date
    May 2002
    Posts
    2,645
    I'm impressed with the effort jmodic put into this. However, since you claim it is not working for you, I suggest one of two courses of action:

    1) Suck up to jmodic and ask him to further refine what he did.

    2) Do the string array routine. That is guaranteed to work. Everytime you hit a space, an insert is made, and the rest of the array is processed. End of the array --> go to the next string in your cursor. Takes a lot more processing, but hey, what do you care? That's what computers are for.

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