Is there a combination of “LIKE” and “IN” in SQL?

Question

In SQL I (sadly) often have to use "LIKE" conditions due to databases that violate nearly every rule of normalization. I can't change that right now. But that's irrelevant to the question.

Further, I often use conditions like WHERE something in (1,1,2,3,5,8,13,21) for better readability and flexibility of my SQL statements.

Is there any possible way to combine these two things without writing complicated sub-selects?

I want something as easy as WHERE something LIKE ('bla%', '%foo%', 'batz%') instead of this:

WHERE something LIKE 'bla%'
OR something LIKE '%foo%'
OR something LIKE 'batz%'

I'm working with SQl Server and Oracle here but I'm interested if this is possible in any RDBMS at all.

Answer 1

In Oracle RBDMS you can achieve this behavior using REGEXP_LIKE function.

The following code will test if the string three is present in the list expression one|two|three|four|five (in which the pipe "|" symbol means OR logic operation).

SELECT 'Success !!!' result
FROM dual
WHERE REGEXP_LIKE('three', 'one|two|three|four|five');

RESULT
---------------------------------
Success !!!

1 row selected.

Preceding expression is equivalent to:

three=one OR three=two OR three=three OR three=four OR three=five

So it will succeed.

On the other hand, the following test will fail.

SELECT 'Success !!!' result
FROM dual
WHERE REGEXP_LIKE('ten', 'one|two|three|four|five');

no rows selected

There are several functions related to regular expressions (REGEXP_*) available in Oracle since 10g version. If you are an Oracle developer and interested this topic this should be a good beginning Using Regular Expressions with Oracle Database.

Answer 2

Starting with 2016, SQL Server includes a STRING_SPLIT function. I'm using SQL Server v17.4 and I got this to work for me:

DECLARE @dashboard nvarchar(50)
SET @dashboard = 'P1%,P7%'

SELECT * from Project p
JOIN STRING_SPLIT(@dashboard, ',') AS sp ON p.ProjectNumber LIKE sp.value

Answer 3

Use an inner join instead:

SELECT ...
FROM SomeTable
JOIN
(SELECT 'bla%' AS Pattern 
UNION ALL SELECT '%foo%'
UNION ALL SELECT 'batz%'
UNION ALL SELECT 'abc'
) AS Patterns
ON SomeTable.SomeColumn LIKE Patterns.Pattern

Answer 4

I was also wondering for something like that. I just tested using a combination of SUBSTRING and IN and it is an effective solution for this kind of problem. Try the below query :

Select * from TB_YOUR T1 Where SUBSTRING(T1.Something, 1,3) IN ('bla', 'foo', 'batz')

Answer 5

One approach would be to store the conditions in a temp table (or table variable in SQL Server) and join to that like this:

SELECT t.SomeField
FROM YourTable t
   JOIN #TempTableWithConditions c ON t.something LIKE c.ConditionValue

Answer 6

In Teradata you can use LIKE ANY ('%ABC%','%PQR%','%XYZ%'). Below is an example which has produced the same results for me

--===========
--  CHECK ONE
--===========
SELECT *
FROM Random_Table A
WHERE (Lower(A.TRAN_1_DSC) LIKE ('%american%express%centurion%bank%')
OR Lower(A.TRAN_1_DSC) LIKE ('%bofi%federal%bank%')
OR Lower(A.TRAN_1_DSC) LIKE ('%american%express%bank%fsb%'))

;
--===========
--  CHECK TWO
--===========
SELECT *
FROM Random_Table  A
WHERE Lower(A.TRAN_1_DSC) LIKE ANY 
('%american%express%centurion%bank%',
'%bofi%federal%bank%',
'%american%express%bank%fsb%')