0

I want to filter all entry where Name is like 'Aay%' , 'Ara%', 'Kim%', .....

For IN, I use:

SELECT *
FROM TABLE 
WHERE NAME IN @List_OF_NAME

For LIKE:

SELECT * 
FROM TABLE
WHERE NAME LIKE 'Aay%'

Is there anyway to combine the two approach. so, that I get all the entry where name is like 'Aay%' , 'Ara%', 'Kim%'.....? Any leads to get this functionality?

EDIT: I tried the approach mentioned here: Is there a combination of "LIKE" and "IN" in SQL?

But, I get this error:

Cannot use a CONTAINS or FREETEXT predicate on column 'Name' because it is not full-text indexed

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Disp
  • 11
  • 4

2 Answers2

0

There is no short-circuit expression for LIKE, as IN for equality. You do need to repeat the expressions:

SELECT * 
FROM MYTABLE
WHERE NAME LIKE 'Aay%' OR NAME LIKE 'Ara%' OR NAME LIKE 'Kim%'

If you have a large list of values, you might want to put them in a derived table, and use EXISTS, like so:

SELECT * 
FROM MYTABLE t
WHERE EXISTS (
    SELECT 1 
    FROM (VALUES ('Aay%'), ('Ara%'), ('Kim%')) x(NAME)
    WHERE t.NAME LIKE x.NAME
)

Expanding that logic, you could very well use a table variable, or a temporary table instead of VALUES().

GMB
  • 216,147
  • 25
  • 84
  • 135
0

Since you are interested in the three first letters for all the cases, you could take the left part and use the IN clause:

SELECT *
FROM MyTable
WHERE LEFT(NAME, 3) IN ('Aay', 'Ara', 'Kim')
Olivier Jacot-Descombes
  • 104,806
  • 13
  • 138
  • 188