MySQL - SUBSTRING_INDEX between multiple points

Question

I know this is far from the optimal way of doing things, but it is something that needs to be done infrequently, so I don't want to look at modifying the database / applications.

I have a database field DataField (BLOB) which contains a string of data in the format:

"1,,0,13424556,item,something"

however that field may contain multiple instances (Sometimes 4 or 5 instances)e.g.

"1,,0,13424556,item,something\r\n"1,,1,12345888,itemOther,somethingElse"

I need to find a way (in MySQL) to split this up to display each item individually (in it's own column). But can't find a way of doing this. (In fact if at all possible I need just the number string from each one (e.g.

13424556

))

I have the below query which is a start:

SELECT
SUBSTRING_INDEX(`DataField`,'\r\n',1),
SUBSTR(`DataField`, LOCATE('\r\n',`DataField`)+2, (CHAR_LENGTH(`DataField`) -     LOCATE('\r\n',REVERSE(`DataField`) )- LOCATE('\r\n',`DataField`))) AS e2
FROM table3;

This works if there are only two items, but not if there are 3 or more.

If anyone can expand on this I would be very grateful.

Tom Mac · Accepted Answer · 2013-11-06 15:12:35Z

1

This might get you going in the right direction:

select 
substring_index(
  substring_index(case when `DataField` like "%\r\n" then `DataField` else concat(`DataField`,"\r\n") end,
  "\r\n",2),
"\r\n",-1)
from table3;

This will return you the second item. Replace the 2 with 3 to get the third, 4 to get the fourth etc etc.

You might have to tweak it a little....

answered Nov 6, 2013 at 15:12

Tom Mac

9,8633 gold badges32 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

IGGt Over a year ago

Cheers, that worked quite nicely for what I need. I'll have to see if I can get it down to just the number that I want now. . .

Collectives™ on Stack Overflow

MySQL - SUBSTRING_INDEX between multiple points

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related