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
– nerdlyist, Commented Oct 13, 2016 at 19:39
If you have a comma separated list in a DB column then you are doing it wrong. — AbraCadaver
– AbraCadaver, Commented Oct 13, 2016 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
– Marc B, Commented Oct 13, 2016 at 19:51

Barmar · Accepted Answer · 2016-10-13 19:38:35Z

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, 2016 at 19:38

Barmar

789k57 gold badges555 silver badges669 bronze badges

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

Comments

Vamsi Prabhala · Accepted Answer · 2016-10-13 19:25:37Z

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, 2016 at 19:25

Vamsi Prabhala

49.4k4 gold badges41 silver badges64 bronze badges

Comments

Amir Ziarati · Accepted Answer · 2016-10-13 19:41:39Z

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, 2016 at 19:41

answered Oct 13, 2016 at 19:29

Amir Ziarati

15.2k12 gold badges51 silver badges54 bronze badges

2 Comments

Juan Carlos Oropeza Over a year ago

what happen if 1 is on the middle?

Juan Carlos Oropeza Over a year ago

what happen if text field is just '1' no more elements so no ','?

vctrd · Accepted Answer · 2016-10-13 19:25:14Z

0

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

answered Oct 13, 2016 at 19:25

vctrd

5185 silver badges10 bronze badges

Comments

Juan Carlos Oropeza · Accepted Answer · 2016-10-13 19:27:11Z

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, 2016 at 19:27

Juan Carlos Oropeza

48.4k14 gold badges87 silver badges128 bronze badges

Comments

Nisse Engström · Accepted Answer · 2016-10-14 16:44:46Z

0

Have you tried:

select rows
from table
where field contains '1,'

?

edited Oct 14, 2016 at 16:44

Nisse Engström

4,76323 gold badges29 silver badges44 bronze badges

answered Oct 13, 2016 at 19:27

Tyler Asbury

11 bronze badge

3 Comments

Tyler Asbury Over a year ago

Sorry--not what I typed at the end.

Tyler Asbury Over a year ago

Select row from table where left(field,2) = '1,'

Tyler Asbury Over a year ago

Are the results always sorted nicely like you indicate, or could they be out of order, like '3,2,1,11' ?

Jonathan Leffler · Accepted Answer · 2017-05-15 02:27:35Z

0

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

edited May 15, 2017 at 2:27

Jonathan Leffler

759k145 gold badges961 silver badges1.3k bronze badges

answered Oct 13, 2016 at 19:27

jjones150

3025 silver badges20 bronze badges

Collectives™ on Stack Overflow

Match part of string (SQL LIKE) In MySQL

7 Answers 7

Comments

Comments

2 Comments

Comments

Comments

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

7 Answers 7

Comments

Comments

2 Comments

Comments

Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related