CODEWITHSUNDEEP
sqldatabasemysql
SimonDowdles
SimonDowdles

Reputation: 2046

MySQL match existence of a term amongst Comma Seperated Values using REGEXP

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 about checking for the existence of the whole phrase 'bank special' only and not partially matching the words?

Many thanks for your time and help.

Upvotes: 1

Views: 612

Answers (3)

jwueller
jwueller

Reputation: 30996

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.

Upvotes: 1

Keeper
Keeper

Reputation: 3566

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($|,)';

Upvotes: 1

Andomar
Andomar

Reputation: 238126

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.

Upvotes: 1

Related Questions