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.

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

Thanks for the reply, it helped....

Can you share with us how you did it, please ?

Cheers
Sathyaram

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

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

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.

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