CODEWITHSUNDEEP
phpmysql
user3741635
user3741635

Reputation: 862

How to search for particular strings between commas in MySQL?

I have table column that contain strings seperated by , like so

Algebraic topology,Riemannian geometries
Classical differential geometry,Noncommutative geometry,Integral transforms
Dark Matter
Spectral methods,Dark Energy,Noncommutative geometry
Energy,Functional analytical methods

I am trying to search for the MySQL row that has a string between comma, for example if I was search for Noncommutative geometry, I want to select these two rows

Classical differential geometry,Noncommutative geometry,Integral transforms
Spectral methods,Dark Energy,Noncommutative geometry

This is what I tried 

SELECT * FROM  `mytable` WHERE  ``col` LIKE  '%Noncommutative geometry%'

which works fine, but there problem is that if I was searching for Energy I want to select the row

Energy,Functional analytical methods

but my code gives the two rows

Energy,Functional analytical methods
Spectral methods,Dark Energy,Noncommutative geometry

which is not what I am looking for. Is there a way to fix this so that it only finds the rows that have the string between commas?

Upvotes: 2

Views: 96

Answers (2)

Robby Cornelissen
Robby Cornelissen

Reputation: 97130

Give these a try, using the REGEXP operator:

SELECT * FROM  `mytable`
WHERE  `col` REGEXP  '(^|.*,)Noncommutative geometry(,.*|$)'

SELECT * FROM  `mytable`
WHERE  `col` REGEXP  '(^|.*,)Energy(,.*|$)'

The expression being used ('(^|.*,)$searchTerm(,.*|$)') requires the search term to be either preceded by a comma or the beginning of the string, and followed by either a comma or the end of the string.

Upvotes: 2

Ahmad Anwar
Ahmad Anwar

Reputation: 67

you can do like this 

SELECT * FROM  `mytable` WHERE  `col` LIKE  '%,$yourString,%'
                         or `col` LIKE '$yourString,%'
                         or `col` LIKE '%,$yourString'

Upvotes: 0

Related Questions