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.

It will never be complete…

That is the best way to describe a true Business Intelligence project. As business processes and applications change, the need for steady development and re-engineering of any centralized BI solution is a constant.

One such project has been a big time sink of mine over the past 18 months, growing and adapting to the changing landscape that describes our (Ocean Cold's) operations. Equally juxtaposed to the frequently changing business processes is the frequently changing inventory management system we are using, Datex Footprint. With each new version comes changes to the database schema and new entities that can be exposed through new Fact/Dimension relationships. A rapid software release cycle ensures that there is always new things on the horizon.