CODEWITHSUNDEEP
phpmysqlsql
Simon Thomsen
Simon Thomsen

Reputation: 1411

Match part of string (SQL LIKE) In MySQL

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.

Upvotes: 1

Views: 110

Answers (7)

jjones150
jjones150

Reputation: 302

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

Upvotes: 0

Tyler Asbury
Tyler Asbury

Reputation: 1

Have you tried:

select rows
from table
where field contains '1,'

?

Upvotes: 0

Amir Ziarati
Amir Ziarati

Reputation: 15087

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"

Upvotes: 1

Barmar
Barmar

Reputation: 780889

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.

Upvotes: 3

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48187

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 + ',%'

Upvotes: 0

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

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

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

Upvotes: 1

vctrd
vctrd

Reputation: 518

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

Upvotes: 0

Related Questions