Search for a column containing CSV data in a MySQL table for input values ​​to exist

Question

I have a say, ITEM table in MySQL that stores data as follows:

ID FEATURES -------------------- 1 AB,CD,EF,XY 2 PQ,AC,A3,B3 3 AB,CDE 4 AB1,BC3 -------------------- 

As input, I get a CSV string, something like "AB, PQ". I want to get records containing AB or PQ. I realized that for this we have to write a MySQL function. So, if we have this magic function MATCH_ANY defined in MySQL that does this, I would simply execute SQL as follows:

 select * from ITEM where MATCH_ANY(FEAURES, "AB,PQ") = 0 

The above query will return records 1, 2 and 3.

But I am doing all kinds of problems when implementing this function, because I realized that MySQL does not support arrays and there is no easy way to split the lines based on the separator.

Remodeling a table is the last option for me, as it has many problems.

I can also execute queries containing several MATCH_ANY functions, such as:

 select * from ITEM where MATCH_ANY(FEATURES, "AB,PQ") = 0 and MATCH_ANY(FEATURES, "CDE") 

In the above case, we get the intersection of records (1, 2, 3) and (3), which will be only 3.

Any help is greatly appreciated.

thanks

Answer 1

First of all, the database should, of course, not contain values ​​separated by commas, but you hopefully already know about it. If the table has been normalized, you can easily get the elements using a query, for example:

 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 strings in comma separated values, but this is not very efficient:

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

Answer 2

 select * from ITEM where where CONCAT(',',FEAURES,',') LIKE '%,AB,%' or CONCAT(',',FEAURES,',') LIKE '%,PQ,%' 

or create a custom function to execute your MATCH_ANY

Answer 3

For all of you REGEXP lovers, I thought I'd add this as a solution:

 SELECT * FROM ITEM WHERE FEATURES REGEXP '[[:<:]]AB|PQ[[:>:]]'; 

and for case sensitivity:

 SELECT * FROM ITEM WHERE FEATURES REGEXP BINARY '[[:<:]]AB|PQ[[:>:]]'; 

For the second request:

 SELECT * FROM ITEM WHERE FEATURES REGEXP '[[:<:]]AB|PQ[[:>:]]' AND FEATURES REGEXP '[[:<:]]CDE[[:>:]]; 

Hurrah!

Answer 4

Alternatively consider using RLIKE ()

  select * from ITEM where ','+FEATURES+',' RLIKE ',AB,|,PQ,'; 

Answer 5

Just a thought:

Does this need to be done in SQL? This is what you can usually expect in PHP or Python or in any other language that you use to interact with the database.

This approach means that you can build your query string using any complex logic, and then just send a query to vanilla SQL, rather than trying to build a procedure in SQL.

Ben