split string in mysql query

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 strin开发者_StackOverflow中文版g can contain more pairs of keys/ values

---

Check out regular expression matching

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

---

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

Just replace ':' with ','.

---

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.