Searching a Column Containing CSV Data in a MySQL Table for Existence of Input Values

Searching a column containing CSV data in a MySQL table for existence of input values

First of all, the database should of course not contain comma separated values, but you are hopefully aware of this already. If the table was normalised, you could easily get the items using a query like:

select distinct i.Itemid
from Item i
inner join ItemFeature f on f.ItemId = i.ItemId
where f.Feature in ('AB', 'PQ')

You can match the strings in the comma separated values, but it's not very efficient:

select Id
from Item
where
  instr(concat(',', Features, ','), ',AB,') <> 0 or
  instr(concat(',', Features, ','), ',PQ,') <> 0

how to select based on comma separated values in column

You can use the in-built find_in_set function.

find_in_set('s3',tags) > 0 and find_in_set('rds',tags) > 0

Search text in fields in every table of a MySQL database

You can peek into the information_schema schema. It has a list of all tables and all fields that are in a table. You can then run queries using the information that you have gotten from this table.

The tables involved are SCHEMATA, TABLES and COLUMNS. There are foreign keys such that you can build up exactly how the tables are created in a schema.

MySQL query finding values in a comma separated string

The classic way would be to add commas to the left and right:

select * from shirts where CONCAT(',', colors, ',') like '%,1,%'

But find_in_set also works:

select * from shirts where find_in_set('1',colors) <> 0

How can I check whether a number is contained in comma separated list stored in a varchar column?

You should really redesign this table to split out those values from being comma separated to being in individual rows. However, if this is not possible, you are stuck with doing string comparison instead:

DECLARE @id INT = 3
DECLARE @stringId VARCHAR(50) = CAST(@id AS VARCHAR(50))

SELECT * 
FROM MyTable 
WHERE categoryIds = @stringId -- When there is only 1 id in the table
OR categoryIds LIKE @stringId + ',%' -- When the id is the first one
OR categoryIds LIKE '%,' + @stringId + ',%' -- When the id is in the middle
OR categoryIds LIKE '%,' + @stringId -- When the id is at the end

check comma separated field against comma separated value in mysql

You can use a regex to check whether the value is contained in cat_id:

SELECT * FROM user WHERE name='test' AND cat_id REGEXP CONCAT('[[:<:]]', value, '[[:>:]]')

this will attempt to match value at any word boundary in cat_id, so for cat_id='1,2,3', values of (for example) '1,2', '2', '2,3' will match.

To put it in a string form (e.g. for PHP):

$sql = "SELECT * FROM user WHERE name='test' AND cat_id REGEXP CONCAT('[[:<:]]','" . $value. "', '[[:>:]]')";

Where value in column containing comma delimited values

There is one tricky scenario. If I am looking for '40' in the list '17,34,400,12' then it would find ",40" and return that incorrect entry. This takes care of all solutions:

WHERE (',' + RTRIM(MyColumn) + ',') LIKE '%,' + @search + ',%'

---

Related Topics