CODEWITHSUNDEEP
mysqldatabase
user2854563
user2854563

Reputation: 268

Find all users having specific number / string in a sql column

I've a db table named "users" which has a column named "alerts". The alert column has the comma separated data in following sequence;

UserA -> 2314|1,2315|1,2316|3
UserB -> 4235|2,2315|1,2314|3
UserC -> 342|5,2314|1,2316|3

Where if I split the comma and then slash separated value then;

2314 = pid
1 = uid

If I want to search all users having specific pid e.g. 2314 and to list them OR fetch them all, how and what SQL query I should have to use?

Upvotes: 0

Views: 1216

Answers (1)

Bohemian
Bohemian

Reputation: 425198

On the face of it, you would search for ',2314|', but the problem is that numbers at the start of the value don't have a preceding comma.

There are 3 ways to solve it.

Handle the start case and middle case separately:

select * from users
where alerts like '2314|%'
or alerts like '%,2314|%'

Combine the cases by adding a comma to the beginning of the value to make it look like a middle case:

select * from users
where concat(',', alerts) like '%,2314|%'

Use a regular expression to combine the cases:

select * from users
where alerts rlike '(^|,)2314\|'

It would be better to redesign your tables to break out a new table to handle the many-to-many relationship you have shoehorned into one column.

Upvotes: 1

Related Questions