MySQL WHERE LIKE substring

Question

I realise the title of this question may scream "duplicate" but I don't think it is (at least if it is, I haven't seen it).

I am currently storing a list separated by commas in the database like 1,3,5,6,13 but when I do SELECT id FROM users WHERE additional_usergroups LIKE '%3%' both 3 and 13 show up.

Is there a way round this?

EDIT
I'm looking for only 3 to show up in the result, sorry for being so vague with the question.

Thanks.

Yes - properly normalize your database. (Or use FIND_IN_SET, if you want to keep the mess you have right now at any cost ...) — C3roe
– C3roe, Commented Aug 22, 2017 at 13:45

Devon Bessemer · Accepted Answer · 2017-08-22 13:52:30Z

5

This is why RDBMS have normalization guidelines. I encourage you to read about normalization and why values in databases should be singular.

But, a way around this would be including every possibility of just 3:

additional_usergroups LIKE '3' 
OR additional_usergroups LIKE '%,3,%'
OR additional_usergroups LIKE '3,%'
OR additional_usergroups LIKE '%,3'

As @CBroe pointed out, MySQL appears to have a FIND_IN_SET function to handle this:

WHERE FIND_IN_SET('3', additional_usergroups) > 0

However, normalization is still recommended for a RDBMS.

edited Aug 22, 2017 at 13:52

answered Aug 22, 2017 at 13:46

Devon Bessemer

35.6k10 gold badges77 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Cody · Accepted Answer · 2017-08-22 13:45:39Z

-3

SELECT id FROM users WHERE additional_usergroups LIKE '3%'

the '%' means grab whatever you can which is why its used for search features so frequently. if you remove it you will only grab numbers that begin with 3, so 3,31,312 etc.

answered Aug 22, 2017 at 13:45

Cody

1491 silver badge10 bronze badges

5 Comments

Spoon Over a year ago

Thanks @Cody but this still wouldn't be useful to me because if I was to have 31 it would pickup that and I wouldn't want it.

Cody Over a year ago

if you just want the '3' then get rid of both '%'. like '3'

MatsLindh Over a year ago

@Cody Then you won't find anything, since the value of the field is "3,1,2,31,4" and not just "3".

Cody Over a year ago

SQL would pull the entire row no matter what right? I dont understand how you would be just getting the 3,13

Cody Over a year ago

You should use a string split to and put the comma separated values inside a temporary table and that will resolve your problem

Collectives™ on Stack Overflow

MySQL WHERE LIKE substring

2 Answers 2

Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related