SQL Code , help with the LIKE statement

[Quackers]

Trying to create a dataset on our internet reporting software which is all done in SQL.

 

Trying to grab google searches with specific words in them. So for example if i use keyword = 'bum' , the search phase 'pokey bum w**k' won't be matched as its looking for exactly the word bum. So i did keyword LIKE '%bum%' and it matches, but so does the word album which is not what i am after. So then i though what about putting a space either side so its keyword LIKE '% bum %' which works for pokey bum w**k, but fails when i then do keyword LIKE '% gay %' if gay is the first word as it does not start with a space.

 

Is there a way in SQL to match any whole words in a string? (and yes the kids have been searching for pokey bum w**k )

Edited March 8, 2016 by elsiegee40
Language please!

[Arreks]

If I remember correctly, you'll have to use regular expressions if you want to return whole words and not parts of words.

[ITGuyWestMidlands]

Pokey what now?

[detjo]

try: LIKE '%[ ]bum[ ]%'

[Quackers]

try: LIKE '%[ ]bum[ ]%'

 

Tried that one, does not return any results when using the [] .

[Arreks]

Furthering my answer, rather than using LIKE use REGEXP i.e this may work:

REGEXP '[[:<:]]bum[[:>:]'

though I haven't tested.

 

The [[:<:]] and [[:>:]] are word boundaries.

Edited March 9, 2016 by Arreks