CODEWITHSUNDEEP
mysqlsql
Dan Hastings
Dan Hastings

Reputation: 3290

MySQL Select Row Where Column Contains a Value

I have tried using 'LIKE' but it runs into problems which i will explain below.

i have a string column that looks like any of these. "1010, 2020, 3030" "1010" ""

I want to be able to see if this string contains a single ID. e.g 2020. if it does then return the row. I tried using like but if the id is 20 it will return the row because 2020 contains 20.

Selecting the entire db and then using a delimiter to go through all the strings will take far too much time. Is it possible to implement this?

Upvotes: 0

Views: 2867

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271241

First, you should not store lists of things in string variables. SQL has a very nice data structure for lists. It is called a table. Each row in such a table would have an id and one value from the list.

That said, sometimes you are stuck with data like this. In that case, you can use find_in-set():

where find_in_set('20', replace(stringcolumn, ', ', ',')) > 0;

You can also do the logic with like, but MySQL has the convenient built-in function for this.

EDIT:

If you want to do this with like:

where concat(',', stringcolumn, ',') like '%,20,%'

Note that the delimiters "protect" the values, so 20 is not confused with 2020.

Upvotes: 3

Marc B
Marc B

Reputation: 360922

This is why you don't store multiple values in a single field. Because your bad design, this is the query structure you'll have to use EVERY SINGLE TIME to compensate for it:

WHERE
       foo = 2020            // exact match, only value in field
    OR foo LIKE '2020,%'     // value is at start of field
    OR foo LIKE '%,2020,%'   // value is somewhere in the middle of the field
    OR foo LIKE '%,2020'     // value is at the end of the field

Or you could have had a properly normalized design, and just done

WHERE childtable.foo = 2020

and be done with it.

Upvotes: 3

Related Questions