Reputation: 3290
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
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
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