CODEWITHSUNDEEP
sqlstringpostgresqlsql-like
Tom Crawford
Tom Crawford

Reputation: 35

Search postgresql database for strings contianing specific words

I'm looking to query a postgresql database full of strings, specifically for strings with the word 'LOVE' in - this means only this specific version of the word and nothing where love is the stem or has that sequence of characters inside another word. I've so far been using the SELECT * FROM songs WHERE title LIKE '%LOVE%';, which mostly returns the desired results.

However, it also returns results like CRIMSON AND CLOVER, LOVESTONED/I THINK SHE KNOWS (INTERLUDE), LOVER YOU SHOULD'VE COME OVER and TO BE LOVED, which I want to exclude as they are specifically the word 'LOVE'.

I know you can use SELECT * FROM songs WHERE title = 'LOVE';, but this will obviously miss any string that isn't exactly 'LOVE'. Is there an operation in postgresql that can return the results I need?

Upvotes: 1

Views: 659

Answers (1)

user330315
user330315

Reputation:

You can use a regular expression that looks for love either with a space before or after, or if the word is at the start or end of the string: 

with songs (title) as (
  values 
     ('Crimson And Clover'),
     ('Love hurts'),
     ('Only love can tear us apart'),
     ('To be loved'),
     ('Tainted love')
)
select *
from songs 
where title ~* '\mlove\M';

The ~* is the regex operator and uses case insensitive comparison. The \m and \M restrict the match to the beginning and end of a word.

returns:

title                      
---------------------------
Love hurts                 
Only love can tear us apart
Tainted love

Online example: http://rextester.com/EUTHKM33922

Upvotes: 1

Related Questions