Search postgresql database for strings contianing specific words

Question

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?

score 1 · Accepted Answer · 2018-03-09 12:36:10Z

1

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

edited Mar 9, 2018 at 12:36

answered Mar 9, 2018 at 12:14

user330315

Sign up to request clarification or add additional context in comments.

2 Comments

Tom Crawford Over a year ago

So I'm trying to run this select * from songs where title ~* ''\mlove\M''; and I'm getting 'Invalid command \mlove'. Do I also need to run the with songs (title) as ( values ... bit to make this work?

user330315 Over a year ago

The with is only there for some sample data. There was a typo: there were too many ' in my query - I fixed that.

Collectives™ on Stack Overflow

Search postgresql database for strings contianing specific words

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related