MySql SELECT by Spliting multiple values separated by ||

Question

Below is the result set from SELECT query,

mysql> select * from mytable where userid =242 ;

+--------+-----------------------------+------------+---------------------+---------------------+
| UserId | ActiveLinks                 | ModifiedBy | DateCreated         | DateModified        |
+--------+-----------------------------+------------+---------------------+---------------------+
|    242 | 1|2|4|6|9|15|22|33|43|57|58 |         66 | 2013-11-28 16:17:25 | 2013-11-28 16:17:25 |
+--------+-----------------------------+------------+---------------------+---------------------+

What I want is to SELECT the records by splitting the Active links columns and associating it with UserId in the below format,

eg,

UserId   ActiveLinks 

242       1
242       2
242       4
242       6

Can anyone help me with this query , as of now nothing coming to my mind. Thanks

duplicate http://stackoverflow.com/questions/6152137/mysql-string-split — Dgan, Nov 13 '14 at 11:41
you'd better fix your database model. Storing delimited values is almost always a bad choice — , Nov 13 '14 at 11:43
Thanks guys , I know structure is bad, so only migrating all old records to new NORMALISED DB Tables — Dimag Kharab, Nov 13 '14 at 11:48
@Ganesh_Devlekar it might be a duplicate but see the answer Gordon has given , its AWESOME. Anyways thanks to you too — Dimag Kharab, Nov 13 '14 at 11:50
@CodingAnt Same logic http://stackoverflow.com/questions/17942508/sql-split-values-to-multiple-rows — Dgan, Nov 13 '14 at 11:52
Every DB dev receives denormalized data at some point. He can create triggers or record the data in a normalized way trough his back end, but the historical data will need to be adjusted to the new normalized rules. — ninjabber, Nov 13 '14 at 11:58

score 1 · Accepted Answer · edited Nov 13 '14 at 12:40

Dealing with lists stored in data is a pain. In MySQL, you can use substring_index(). The following should do what you want:

SELECT userid,
       substring_index(substring_index(l.ActiveLinks, '||', n.n), '|', -1) as link
FROM (select 1 as n union all select 2 union all select 3 union all select 4) n join
     ipadminuserslinks l
     on length(l.ActiveLinks) - length(replace(l.ActiveLinks, '||', '')) + 1 <= n.n
WHERE userid = 242;

The first subquery generates a bunch of numbers, which you need. You may have to increase the size of this list.

The on clause limits the numbers to the number of elements in the list.

As you can probably tell, this is rather complicated. It is much easier to use a junction table, which is the relational way to store this type of information.

score 0 · Answer 2 · answered Nov 13 '14 at 11:52

I would create a routine which will have the delimiter as an argument. Another in_var would be the correspondent line.

Every time you call it, it will return the set of values for the UserId called. It will basically use a loop based on the count of '|' (we call this pipeline) This way you can implement the solution proposed by @Gordon Linoff without the need to know how many active links you have.

score 0 · Answer 3 · answered Nov 13 '14 at 12:24

If this is just a list of values that do not relate to anything on another table I would do it the same way as Gordon (if needs be you can cross join the sub query that gets the lists of numbers to easily generate far larger ranges of numbers). One minor issue is that if the range of number is bigger than the number of delimited values on a row then the last value will be repeated (easily removed using DISTINCT in this case, more complicated when there are duplicate values in there that you want to keep).

However if the list of delimited values are related to another table (such as being the id field of another table then you could do it this way:-

SELECT a.UserId, b.link_id
FROM mytable a
LEFT OUTER JOIN my_link_table b
ON FIND_IN_SET(b.link_id, replace(a.ActiveLinks, '|', ','))

Ie, use FIND_IN_SET to join your table with the related table. In this case converting any | symbols used as delimiters to commas to allow FIND_IN_SET to work.

MySql SELECT by Spliting multiple values separated by ||

3 Answers3