Mysql delimited substrings...

[JukesK]

firstly I apologise in advance if this has been asked 100 times over..

I've been ambushed by my company i "HAVE" to use an old Mysql 4.1 server something to do with an atiquated system still running that cannot be upgraded for x,y and z reasons. but they want a new report running..

If i have a comma delimited field with 5 values in can i return a specific value without the rest

ie

colA

Val1, Val2, Val3, Val4, Val5

Select
Substring_index(colA, ',' , -3) As Value 3
from table 

can it just return val3 and not val3, val4, val5

I cannot use a substring length as the data value changes based on user input and is anywhere between 3 - 255 chars per value.

Many Many thanks in advance for your help....

Regards
KeeF

[pritaeas]

Am sure you can make a function that would return such a value.

[JukesK]

Hi Pritaeas,

Thanx for the reply, i did manage to sort it out before finishing work last night...

i used the following code which worked great for me... (cleaned it up to not include company information :) )

hope it will help someone else oneday..

SUBSTRING_INDEX(tablename.columnname, ',', 1) As 'Value 1',
substring(SUBSTRING_INDEX(tablename.columnname, ',', 2),19) As 'Value 2',
trim(leading ',' FROM substring(SUBSTRING_INDEX(tablename.columnname, ',', 3),-7)) As 'Value 3',
substring(SUBSTRING_INDEX(tablename.columnname, ',', -3),1, 3) As 'Value 4',
trim(trailing ',' FROM SUBSTRING_INDEX(tablename.columnname, ',', -2)) As 'Value 5',

Thanx for the response again

KeeF