Results 1 to 8 of 8
  1. #1
    Join Date
    May 2002
    Posts
    15

    Unanswered: split function in sql

    Hi,

    I have a column in the tables as follows:

    Table X
    A
    12345,1234123,123
    34523,2312,232
    75657,34334,123

    Table Y
    B C D
    12345 1234123 asd
    34523 2312 dafd
    75657 34334 sda

    I need to split the data that is comma delimited in Table X and join it with column B and C in Table Y. Is there a way i can do it in sql.

    Ex: 1st row, i need to split to get: 12345 1234123 123
    now i need to use 12345 and 1234123 to join with column in other table.

    I can not use substr() function as the length might vary.

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: split function in sql

    I think you can achieve split with a combination of LOCATE, POSSTR, SUBSTR Functions



    Originally posted by huskers
    Hi,

    I have a column in the tables as follows:

    Table X
    A
    12345,1234123,123
    34523,2312,232
    75657,34334,123

    Table Y
    B C D
    12345 1234123 asd
    34523 2312 dafd
    75657 34334 sda

    I need to split the data that is comma delimited in Table X and join it with column B and C in Table Y. Is there a way i can do it in sql.

    Ex: 1st row, i need to split to get: 12345 1234123 123
    now i need to use 12345 and 1234123 to join with column in other table.

    I can not use substr() function as the length might vary.
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    May 2002
    Posts
    15
    Thanks for the reply, it helped....

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Can you share with us how you did it, please ?

    Cheers
    Sathyaram

    Originally posted by huskers
    Thanks for the reply, it helped....
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  5. #5
    Join Date
    May 2002
    Posts
    15
    The first part of the data that i am using contains a unix time so this made things easy for me. The second part keeps varying. I just need to pasrse out the first and second parts of comma delimited data.

    Table X:
    A
    1071503689,58500,4029631200
    1071503689,5850013,4029631200

    so i used the following query:

    select A,substr(A, 1, 10), substr(A,12,posstr(substr(A, 12, locate(',',A,12)),',')-1) from X

    Let me know if we can improve this further.

    Thanks

  6. #6
    Join Date
    May 2002
    Posts
    15
    this is more generalized:

    select A,substr(A,1,locate(',',A)-1), substr(A,12,posstr(substr(A, 12, locate(',',A,12)),',')-1) from X

  7. #7
    Join Date
    May 2002
    Posts
    15
    I was able to parse the 1st and 2nd parts of the string using the following query but am unable to get the 3rd part.

    SELECT
    A,
    substr(A,1,locate(',',A)-1),
    substr(A,locate(',',A)+1,posstr(substr(A, locate(',',A)+1, locate(',',A,locate(',',A)+1)),',')-1)
    FROM X


    Any advice.

  8. #8
    Join Date
    Aug 2003
    Location
    Massachusetts, USA
    Posts
    106
    Here is another possibility. Seems to work (tested with the data you provided):

    SELECT
    A,
    substr(A,1,locate(',',A)-1),
    substr(substr(A,locate(',',A,locate(',',A))+1),1,p osstr(substr(A,locate(',',A,locate(',',A))+1),',')-1),
    substr(A,locate(',',A,locate(',',A)+1)+1)
    FROM X

    Originally posted by huskers
    I was able to parse the 1st and 2nd parts of the string using the following query but am unable to get the 3rd part.

    SELECT
    A,
    substr(A,1,locate(',',A)-1),
    substr(A,locate(',',A)+1,posstr(substr(A, locate(',',A)+1, locate(',',A,locate(',',A)+1)),',')-1)
    FROM X


    Any advice.

Posting Permissions

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