SQL query combination of like and in?

Question

a. Select * from tableA where columnA like '%complete%';
b. Select * from tableA where columnA in ('complete','request');

The possible value for columnA is complete, completed, request, requested..............

My objective is to query those with value complete, completed, request, requested

Normally we will write query where columnA in ('complete','completed','request','requested');

Is there a way to write a much shorter query like Select * from tableA where columnA in like (%complete%, %request%) ?

Answer 1

You need to use multiple OR:

select * 
from tableA 
where columnA like '%complete%'
   or columnA like '%requested%'
   or ...

Use Join:

SELECT *
FROM tableA t
JOIN VALUES (('%complete%'), ('%requested%'), ...) v(c)
  ON t.columnA LIKE v.c

Be aware that search pattern %phrase% is not SARG-able and query optimizer won't use index on that column, if exists any.

You should consider usage of Full-Text Search

Answer 2

You can try using CONTAINS like this:

select * from tableA
WHERE CONTAINS(columnA, '"complete*" OR "requested*" OR ...')