How to find strings without different words?-CodePudding

For example, I'm using SQL to filter out all descriptions containing the fruit 'Plum'. Unfortunately, using this code yields all sorts of irrelevant words (e.g. 'Plump', 'Plumeria') while excluding anything with a comma or full stop right after it (e.g. 'plum,' and 'plum.')

SELECT winery FROM winemag_p1
WHERE description LIKE '%plum%' OR

Is there a better way to do this? Thanks. I'm using SQL Server but curious how to make this work for MySQL and PostgreSQL too

CodePudding user response：

Try the following method, using translate* to handle edge-case characters, search for the keyword including spaces and concat spaces to the source:

with plums as (
  select 'this has a plum in it' p union all
    select 'plum crazy' union all
    select 'plume of smoke' union all
    select 'a plump turkey' union all
    select 'I like plums.' union all
    select 'pick a plum, eat a plum' 
)
select * 
from plums
where Concat(' ',Translate(p,',.s','   '), ' ') like '% plum %'

* assuming you're using the latest version of SQL Server, if not will need nested replace()

CodePudding user response：

Solution (I wasn't able to try on SQL Server, but it should work):

SELECT winery FROM winemag_p1
WHERE description LIKE '% plum[ \.\,]%'

In MySQL you can use the REGEXP_LIKE command (works on 8.0.19) (docs):

SELECT winery FROM winemag_p1
WHERE REGEXP_LIKE(description, '% plum[ .,]%');