CODEWITHSUNDEEP
mysqlselect
Klaus
Klaus

Reputation: 25

How to check if field contains a specific value in mysql?

A table field type string can contain comma separated int values e.g. "1,6,2,13" or "3,9,2" or "1,3"

How can I select all entries which are containing e.g. "3" not getting the "13"?

A select * from table where field like '%3%' will not work in that case. Nor like '%,3,%

Currently I'm trying all combinations: '3,%' , '%,3' , '%,3,%', but I'm wondering if there's a more easy way.

Upvotes: 0

Views: 3432

Answers (2)

SMA
SMA

Reputation: 37023

Try using regex like:

SELECT * 
FROM table 
WHERE field REGEXP '^3,|,3,|,3$';

Upvotes: 0

Girish
Girish

Reputation: 12127

LIKE won't work for exact search, Better to use REGEX to get exact search

SELECT * FROM table WHERE 
    field  REGEXP '[[<:]]3[[:>]]'

Or you can also use FIND_IN_SET() function

SELECT * FROM tblname WHERE 
    FIND_IN_SET('3', field ) > 0

NOTE: FIND_IN_SET() function won't work correctly if tags not symmetric comma separated, if tags have white space between , then it would create problem

Upvotes: 1

Related Questions