MySQL match existence of a term amongst comma seperated values using REGEXP

Question

Hi There,

I have a MySQL select statement dilemma that has thrown me off course a little. In a certain column I have a category term. This term could look something like 'category' or it could contain multiple CSV's like this: 'category, bank special'.

I need to retrieve all rows containing the term 'bank special' in the comma separated value. Currently I am using the following SQL statement:

SELECT * FROM tblnecklaces WHERE nsubcat REGEXP 'bank special';

Now this works OK, but if I had the category as follows: 'diamond special' for example then the row is still retrieved because the term 'special' in my column seems to be matching up to the term 'bank special' in my REGEXP statement.

How would I go abut checking for the existence of the whole phrase 'bank special' only and not partially matching the words?

Many thanks for your time and help

Answer 1

The simplest solution is to use the LIKE clause (% is wildcard):

SELECT * FROM tblnecklaces WHERE nsubcat LIKE '%bank special%';

Note that LIKE is also a lot faster than REGEXP.

Answer 2

You can prefix the column with comma's, and compare it to the bank special:

SELECT  * 
FROM    tblnecklaces
WHERE   ',' + replace(nsubcat,', ','') + ',' LIKE ',bank special,'

I put in a replace to remove optional space after a comma, because your example has one.

Answer 3

Not tested but this RegExp should work (LIKE works too but will match items that starts or ends with the same phrase):

SELECT * FROM tblnecklaces WHERE nsubcat REGEXP '(^|, )bank special($|,)';