Searching a table with SQL LIKE statement and multiple paramters

Recently I was faced with the need to search across a table for several search terms and return the results. The search terms were to be built dynamically, so there was no way to know how many would be provided. Due to some client policies, I had to do the work through a stored procedure. I did not want to perform multiple calls to the procedure for each term identified, but you cannot pass multiple parameters to the LIKE statement in SQL, or can you? I immediately thought of passing in all my search terms via XML, but couldn't remember the syntax to pull the data out. Thankfully a friend of mine was kind enough hear my dilema and not laugh (too hard) when he pointed out that it was called 'OPENXML'. Yes, extremely obvious, but it had been a long day. Ok, so back on track.


If you haven't used OPENXML before it is available in SQL Server 2005/2008 and is really quite useful. In the past, I have used this mostly for passing in multiple objects to perform a mass INSERT and/or UPDATE. This was my first time using it with a LIKE statement, but it works perfectly. I didn't want to lose this code, since I am likely to use it again in the future so I figured I would do a quick blog post. So here is how to pass multiple parameters into a LIKE statement in SQL Server.

Below the schema for a table that I will use in my example.

Here is a little sample data for as well


So I need to be able to search the "Bio" field in my table to see if it contains the words 'PhD', 'scientist' or 'CEO'.

First, I need to build my search terms in XML form. This is a simple string which can be built in your application code and passed as a parameter into SQL Server. My example below show how you can do it in SQL Server so you can tweak it.

--String parameter with the search terms in XML format

DECLARE @xmlTerms VARCHAR(8000) -- Size it according to your expectations



--Here I am building the XML string with all the necessary terms.

SET @xmlTerms =

'<SearchTerms>

<SearchTerm>%PhD%</SearchTerm>

<SearchTerm>%scientist%</SearchTerm>

<SearchTerm>%CEO%</SearchTerm>

<SearchTerms>'



--A handle to the XML so we can reference it in the OPENXML call

DECLARE @xmlHandle INT



--This parses the XML and returns us a handle to the data

EXEC sp_xml_preparedocument @xmlHandle out, @xmlTerms



--Now we can perform our query

SELECT id, searchTerm FROM exampleTable A

INNER JOIN (SELECT searchTerm

FROM OPENXML(@xmlHandle,'/SearchTerms/SearchTerm',1) --Make note that the XML nodes are case sensitive

WITH (searchTerm VARCHAR(50) 'text()')) T ON A.Bio LIKE searchTerm

ORDER BY id 

One key thing to note is that this must be done via a JOIN, otherwise it will not work. The JOIN is what allows the LIKE comparison over multiple criteria dynamically.