How to achieve the LIKE IN effect in SQL Server

SQL Server does not natively support a “LIKE IN” clause. For instance the following example is not valid:

SELECT t.*
FROM MyTable t
WHERE t.Description like in ('%word1%', '%word2%');

Luckily there is a way to achieve this effect.

First imagine that you have a stored procedure or function that searches for a specified word that appears anywhere within a Description column. If you had to search for only one string within the description, your query is simple and would look like this:

SELECT t.*
FROM MyTable t
WHERE t.Description like '%' + @MyDescription + '%';

If you have to search for the occurrence of ‘apple’ then the variable @MyDescription would have to be set to ‘apple’.

If, instead, you were given a list of strings and you need to find all Descriptions that contain at least one of those words in the list, the above query would not work. The example below shows how the above query needs to be modified in order to achieve this.

If you have to search for the occurrence of at least one of the words in ‘apple,pear,peach’ then the variable @MyDescription would have to be set to ‘apple,pear,peach’. Note the words are comma separated in this example, but you could use any other character as a separator, if needed.

-- Separate comma separated values and insert into temp table.
CREATE TABLE #DescTab (Description varchar(40))
declare @DescXml xml, @DescList varchar(200)
SET @DescList = '<x><v>'
              + REPLACE(@MyDescription, ',', '</v></x><x><v>' )
              + '</v></x>'
SET @DescXml = convert(xml, @DescList)
INSERT INTO #DescTab (Description)
SELECT v = RTRIM(LTRIM(T.c.value('v[1]', 'varchar(40)')))
FROM @DescXml.nodes('x') T(c)

-- Select the Descriptions that contain one of the words in
-- the specified list.
SELECT DISTINCT t.*
FROM MyTable t
     left outer join #DescTab d
             on len(@MyDescription) = 0
             or patindex('%' + d.Description + '%', t.Description) > 0
WHERE (len(@MyDescription) = 0 or d.Description is not null)

Leave a Reply

Your email address will not be published. Required fields are marked *

*