Match part of string (SQL LIKE) In MySQL

Question

I've a field that looks like this

1,13,15

If I try to make a search to find all rows than contains "1", then it's taking all rows that have 1 inside, and not only rows that says 1, [something], but also "11","13" etc.

I've tried with a like statement and wildcards, but without luck.

Instead of taking this at face value. I am curious what this field is for/of? What are these numbers for? Could there be a better database setup? — nerdlyist, Oct 13 '16 at 19:39
If you have a comma separated list in a DB column then you are doing it wrong. — AbraCadaver, Oct 13 '16 at 19:40
Proper solution is to fix your table so you don't have CSV data in a field. Normalize and put those values into a child table, and then your problem is moot. — Marc B, Oct 13 '16 at 19:51

score 3 · Answer 1 · answered Oct 13 '16 at 19:38

3

If you're using MySQL, use FIND_IN_SET, not LIKE.

WHERE FIND_IN_SET('1', columnname)

But the best solution is to normalize your schema so you don't have comma-separated values in a column.

answered Oct 13 '16 at 19:38

Barmar

741,623
53
500
612

score 1 · Answer 2 · answered Oct 13 '16 at 19:25

1

If you are using MySQL you can use regexp to check such values

where column_name regexp '^1,|,1,|,1$|^1$'

answered Oct 13 '16 at 19:25

Vamsi Prabhala

48,685
4
36
58

Amir Ziarati · Answer 3 · 2016-10-13T19:41:39.577

1

When you say "1*" it finds everything that has a one and anything after that. Just narrow down your search and serach for:

 field LIKE "1,%" OR field LIKE "%,1,%" OR field LIKE "%,1" OR field = "1"

edited Oct 13 '16 at 19:41

answered Oct 13 '16 at 19:29

Amir Ziarati

14,248
11
47
52

what happen if `1` is on the middle? – Juan Carlos Oropeza Oct 13 '16 at 19:30
what happen if text field is just `'1'` no more elements so no `','`? – Juan Carlos Oropeza Oct 13 '16 at 19:40

score 0 · Answer 4 · answered Oct 13 '16 at 19:25

0

You can search for "1," OR "1" OR ", 1".

answered Oct 13 '16 at 19:25

vctrd

488
4
9

score 0 · Answer 5 · answered Oct 13 '16 at 19:27

0

if your field is '1,13,15' change it to ',1,13,15,'

and your search to LIKE '%,1,%'

So depending on your db you should try something like this

 SELECT  * 
 FROM yourTable
 WHERE ','  + yourField + ',' LIKE  '%,' + @search + ',%'

answered Oct 13 '16 at 19:27

Juan Carlos Oropeza

47,252
12
78
118

score 0 · Answer 6 · edited Oct 14 '16 at 16:44

0

Have you tried:

select rows
from table
where field contains '1,'

?

edited Oct 14 '16 at 16:44

Nisse Engström

4,738
23
27
42

answered Oct 13 '16 at 19:27

Tyler Asbury

1
1

Sorry--not what I typed at the end. – Tyler Asbury Oct 13 '16 at 19:28
Select row from table where left(field,2) = '1,' – Tyler Asbury Oct 13 '16 at 19:28
Are the results always sorted nicely like you indicate, or could they be out of order, like '3,2,1,11' ? – Tyler Asbury Oct 13 '16 at 19:30

score 0 · Answer 7 · edited May 15 '17 at 02:27

0

If you're using MS SQL Server, you should use LIKE '%1%'.

edited May 15 '17 at 02:27

Jonathan Leffler

730,956
141
904
1,278

answered Oct 13 '16 at 19:27

jjones150

168
2
14

Match part of string (SQL LIKE) In MySQL

7 Answers7