0

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

Edited by pritaeas: Fixed markdown.

2
Contributors
2
Replies
6
Views
4 Years
Discussion Span
Last Post by JukesK
0

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

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.