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

I have a table say, ITEM, 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 an input, I will get a CSV string, something like “AB,PQ”. I want to get the records that contain AB or PQ. I realized that we’ve to write a MySQL function to achieve this. So, if we have this magical function MATCH_ANY defined in MySQL that does this, I would then simply execute an SQL as follows:

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

The above query would return the records 1, 2 and 3.

But I’m running into all sorts of problems while implementing this function as I realized that MySQL doesn’t support arrays and there’s no simple way to split strings based on a delimiter.

Remodeling the table is the last option for me as it involves lot of issues.

I might also want to execute queries containing multiple 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 would get an intersection of records (1, 2, 3) and (3) which would be just 3.

Any help is deeply appreciated.

Thanks

Contents hide

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

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

Method 2

For all you REGEXP lovers out there, I thought I would 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 query:

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

Cheers!

Method 3

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

or create a custom function to do your MATCH_ANY

Method 4

Alternatively, consider using RLIKE()

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

Method 5

Just a thought:

Does it have to be done in SQL? This is the kind of thing you might normally expect to write in PHP or Python or whatever language you’re using to interface with the database.

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

Ben

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes

Article Rating