Software Developer's Resources

Sql Server: How to Combine IN and LIKE operators

This article describes how to write a SQL WHERE clause perform the functionality of the IN and LIKE operators.

Suppose, we have a table with a varchar(20) column named ClaimNumber.

If we search for a single claim number we'd write a SELECT statement with this WHERE clause:

SELECT *
    FROM
        Claims
    WHERE ClaimNumber LIKE '%189%'

Suppose, we want to search for several claim numbers using the LIKE operator functionality as we deomstrated above.

Out initial impule would be to write this WHERE clause:

SELECT *
    FROM
        Claims
    WHERE ClaimNumber IN ('%189%', '%025%', '%517%')

We will not get the desired records with the above WHERE clause. SQL won't interpret the '%' wildcard character as when used with the LIKE operator. clause.

Use the OR and LIKE operators to find the desired records.

Change the WHERE clause to:

SELECT
    *
    FROM
        Claims
	WHERE
		ClaimNumber LIKE '%189%'
		OR ClaimNumber LIKE '%025%'
		OR ClaimeNumber LIKE '%517%'