0

I have a column in my DB that has the following data (yeah i know its wrong to have multiple names separated by some random character)

"John Cusack | Thandie Newton | Chiwetel Ejiofor"

I want to be able to separate these people into an array to use later or even just to be able display them like below will help

John Cusack
Thandie Newton
Chiwetel Ejiofor

any ideas please thanks in advance

John Conde
  • 217,595
  • 99
  • 455
  • 496
user1984947
  • 1
  • 1
    You mean in pure mySQL? That may be impossible. Are you accessing the data from an application? Then do it in there. Or of course store the data properly, but you already know that.... – Pekka Jan 16 '13 at 19:49
  • 1
    not easy to do in mysql. do it client-side, then start planning on normalizing your db so you're not stuck with this again in the future. – Marc B Jan 16 '13 at 19:49
  • What scripting language are you using? PHP? With PHP you can pull the field, explode the string on the pipe | and then trim the white space and put into an array. – donlaur Jan 16 '13 at 19:51

2 Answers2

0

As you say, storing delimited lists in an RDBMS really is not a good idea; however, you may be able to use MySQL's string manipulation functions such as SUBSTRING_INDEX() to obtain your desired results (MySQL doesn't have array types, so I assume you're merely looking to split the data):

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(my_column, '|', 1), -1),
       SUBSTRING_INDEX(SUBSTRING_INDEX(my_column, '|', 2), -1),
       SUBSTRING_INDEX(SUBSTRING_INDEX(my_column, '|', 3), -1)
FROM   my_table

Note that one doesn't actually need to invoke SUBSTRING_INDEX() twice for the first and last elements of the list, but I thought it informative to do so in order that the pattern for further elements can be seen more clearly.

If you were so inclined, you could build a stored procedure that loops over the string populating a temporary table with each found element—but this is all so far away from "good practice" that it's almost certainly not worth delving into it any further.

Community
  • 1
  • 1
eggyal
  • 122,705
  • 18
  • 212
  • 237
0

you can try this. 

 select substring_index(substring_index('a|b|c|h', '|',@r:=@r+1),'|',-1) zxz
from (select @r:=0) x,
(select 'x' xx union select 'v' xx union select 'z' xx  union select 'p' xx) z;

Result looks like

----
|zxz|
-----
|a   |
------
|b   |
------
|c   |
------
|h   |
------

locatet here: Mysql and a little modified.

Remember: The "count" of the union statements have to be the same as your delemiter.

Kind Regars

user1116033
  • 474
  • 5
  • 9