split string in mysql query

Question

i have field that has records like '1:23,2:23' and '2:43'

is there any way to split string into 1,23,2,23 (separate values, so i can work with them independently) using mysql query. for example add them to another table like

 row1:   1 | 23
 row2:   2 | 43

the string can contain more pairs of keys/ values

http://stackoverflow.com/questions/1096679/can-mysql-split-a-column — Joshua Enfield, Dec 10 '10 at 15:39
the main problem is i don't know exactly how many pairs is in the field. it can be like '1:jigtrg,2:ew,3:wer' etc — samrockon, Dec 10 '10 at 15:44

score 0 · Answer 1 · answered Dec 10 '10 at 15:41

0

Check out regular expression matching

http://dev.mysql.com/doc/refman/5.1/en/regexp.html

answered Dec 10 '10 at 15:41

kobrien

2,661
2
21
33

score 0 · Answer 2 · answered Dec 10 '10 at 15:42

0

http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_replace

Just replace ':' with ','.

answered Dec 10 '10 at 15:42

Vjy

2,076
3
20
32

score 0 · Answer 3 · answered Dec 10 '10 at 15:44

I may misunderstand your question but here are my thoughts...

SQL is meant to fetch data, not parse it. Parsing should happen on the application side. If you need to create a list for something like an IN clause then you probably need to reconsider you data-model to allow the values you would normally parse out to be in separate rows.

I am not saying you can't do some string magic in SQL but it's usually a sign of a design issue.

split string in mysql query

3 Answers3