How to find strings without different words?

Question

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

``SELECT winery FROM winemag_p1 WHERE description LIKE '% plum %' OR description LIKE '% plum, %' OR description LIKE '% plum. %';`` — kaffarell, Jun 21 '22 at 07:11
What version of SQL are you using (e.g. MySQL, SQL Server, Oracle, Postgres, etc.) ? — Tim Biegeleisen, Jun 21 '22 at 07:11
I'm using SQL Server. I'd rather not have to include a new OR for '?' and '!' too hence looking for a better method. — yudontfly, Jun 21 '22 at 07:12
I think SQL Server also supports regex (https://stackoverflow.com/questions/8928378/using-regex-in-sql-server) — kaffarell, Jun 21 '22 at 07:14

score 0 · Answer 1 · edited Jun 21 '22 at 08:16

0

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[ .,]%');

edited Jun 21 '22 at 08:16

Dale K

25,246
15
42
71

answered Jun 21 '22 at 07:20

kaffarell

639
5
16

No that won't work on SQL server - there is no regex at all. – Dale K Jun 21 '22 at 08:05
1

it seems SQL Server supports limited regex function. tried 2014 above example works. – AIMIN PAN Jun 22 '22 at 00:48
however this sql doesn't work if description is only "plum'. – AIMIN PAN Jun 22 '22 at 00:52

score 0 · Answer 2 · answered Jun 21 '22 at 07:59

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()

score 0 · Answer 3 · answered Jun 22 '22 at 00:55

Tried with sql server 2014 with this sql:

select * from winemag_p1
 where description like '%plum%'
 and not description like '%plum[a-zA-Z0-9]%'
 and not description like '%[a-zA-Z0-9]plum%'

with table content

a plum
b plumable
c plum.
d plum,blum e aplum
f bplummer

it outputs

a           plum      
c           plum.     
d           plum,blum

How to find strings without different words?

3 Answers3