Select query where in with partial match

Question

I would like to select all rows from [Column] that have within their string the words in a list as so:

select [Column]
from [db]
where [Column] like '%some%' or 
      [Column] like '%word%' or
      [Column] like '%and%' or
      [Column] like '%another%'

However, as I have a long list of words, I do not want to repeat the or [Column] like '%%'. Is there a better way to write this? I am using SQL Server Management Studio.

The `or` is probably the best way to do this, unless you have a full text index. — Gordon Linoff, Jan 29 '19 at 12:18

score 3 · Accepted Answer · answered Jan 29 '19 at 08:44

Use a temporary or variable table and move the WHERE to the JOIN.

DECLARE @PartialMatches TABLE (PartialMatch VARCHAR(100))

INSERT INTO @PartialMatches (PartialMatch)
VALUES ('some'),('word'),('and'),('another')

select 
    D.[Column]
from 
    [db] AS D 
    INNER JOIN @PartialMatches AS P ON D.[Column] LIKE '%' + P.PartialMatch + '%'

You can opt to include the like special characters (%) on the table or outside. Having them on the table gives more flexibility, you can put some% and %word, for example.

score 0 · Answer 2 · answered Jan 29 '19 at 08:49

This one (match list or not match list) works for me...

declare @matchs table ( match varchar(50));
declare @notmatchs table ( notmatch varchar(50));
insert into @matchs (match) values ('%matchfield1%');
insert into @notmatchs (notmatch) values ('%nomatch1'), ('nomatch2%');

select e.* 
from mytable e
where not exists (select top 1 1 from @notmatchs nm where e.ObjectName like nm.notmatch)
and exists (select top 1 1 from @matchs m where e.ObjectName like m.match)

score -1 · Answer 3 · answered Jan 29 '19 at 08:41

-1

You can write your query in below format.

select [Column]
from [db]
where [Column] like '%[some-word-and-another]%'

answered Jan 29 '19 at 08:41

A.M. Patel

334
2
9

3

this will not give the same output as the OP is expecting. – PSK Jan 29 '19 at 09:01
1

It will not provide the desired output – Alexander Volok Jan 29 '19 at 09:37
yes, this is not correct, it just gives me everything where is not null or blank – callmeGuy Jan 29 '19 at 09:39
Yes, I have to check my answer and it returns all the row which matches like condition text – A.M. Patel Jan 29 '19 at 10:12

Select query where in with partial match

3 Answers3