Keyboard not found, Press F1 to Continue Random musings from a techhead.

Searching inside strings (nvarchar) in SQL using LIKE

Recently I created a simple report to allow users to search for Items in our Warehouse system that contained a word the user specified. The word the user was searching for would be contained in the Item table's Name column. Below is a simple example of how to do this using a LIKE clause inside the WHERE statement.

--Declare variable
DECLARE @searchstring nvarchar(max)

--Set values for search (You would normally pass this in with a report parameter)
SELECT @searchstring = 'Widget'

--Perform Search
SELECT Items.id, Items.Name
FROM Items
WHERE Items.Name LIKE '%' + @searchstring  + '%'

Note the wildcards added around the variable so that it will search for the user's string at any position in the original data. If @searchstring is NULL, the search will be ignored and in the above example, all rows will be returned from the Items table (this is useful in SSRS since the parameter can be made nullable and therefore optional). Warning: This is not optimized for speed, it is likely very slow on large record sets.