Exact string search with LIKE

Question

I have a table column that includes string like:

1,3,10,15

or

2,1,4,5...

Amount of numbers can change. I need a regular expression that will bring me the column that includes exactly "1" for the first string "1,3,10,15".

It shouldn't choose it because of 10 or 15, so "3, 10, 15" for this string, it shouldn't be selected.

Probably duplicated: http://stackoverflow.com/questions/28928888/mysql-like-query-exclude-numbers/28929084#28929084 Instead of a REGEXP you should use the FIND_IN_SET function. — Jordi Llull, Mar 08 '15 at 18:43
Thanks, trying to figure it out, but this is working for me now REGEXP ',?(x),?' FIND_IN_SET works better i think. — , Mar 08 '15 at 18:48
Keep in mind that having an "array" in a column is poor design. Usually it is better is to have another table with pairs. — Rick James, Mar 08 '15 at 21:42
I needed a generic solution. I do not know the number of items in the array and keeping a certain amount of columns is worse. Let's say I keep 20 columns in a table. If 1 column is needed in one case, the other 19 columns would be empty. And it wouldn't work if I needed 21 columns. Not mentioning keeping 20 columns in one table is a bad solution. — , Mar 09 '15 at 11:16

score 0 · Answer 1 · edited Mar 08 '15 at 18:59

0

If you really want to do this with a like, try this:

Say your column is named numstring.

"," ||numstring||"," like "%,1,%"

edited Mar 08 '15 at 18:59

Bhargav Rao

answered Mar 08 '15 at 18:47

avk

Thanks for the help, solved it with FIND_IN_SET it is meant for cases such as mine – Mar 08 '15 at 19:16

dszakal · Answer 2 · 2015-03-08T19:25:24.477

0

Just use:

WHERE (colname LIKE '1,%' OR colname = '1')

as

SELECT * FROM
(
SELECT '1,2,3' as teststring
) as test
WHERE teststring LIKE '1,%'
;

returns the row "1,2,3" and

SELECT * FROM
(
    SELECT '12,22,23' as teststring
) as test
WHERE teststring LIKE '1,%'
;

returns nothing as expected

edited Mar 08 '15 at 19:25

answered Mar 08 '15 at 18:48

dszakal

What happens if the string is something like "2,10,12,1,20,30"? Still, thanks. – Mar 08 '15 at 19:15
It returns nothing as you only wanted if it starts with 1. Tested on "2,22,23,1,20", "2,3,4,31,12". Like '1,%' means first character must be 1, second must be ',', after that whatever can occur. You can also use my query to test it yourself, just rewrite the part before "as teststring" and you'll see if you wanted the same – dszakal Mar 08 '15 at 19:20
Also if you want to include, so "2,10,12,1,20,30" should be matching, then just add to the WHERE condition OR colname LIKE '%,1,%' OR colname LIKE '%,1' – dszakal Mar 08 '15 at 19:24

2 Answers2