CODEWITHSUNDEEP
mysqlsqldatabase
Dimag Kharab
Dimag Kharab

Reputation: 4519

MySql SELECT by Spliting multiple values separated by ||

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

Upvotes: 2

Views: 246

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

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.

Upvotes: 1

Kickstart
Kickstart

Reputation: 21513

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.

Upvotes: 0

ninjabber
ninjabber

Reputation: 371

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.

Upvotes: 0

Related Questions