05 Sep 2010Multi-value LIKE parameters in SQL
Thinking in SQL, thinking in terms of sets took me a while to achieve. In the beginning I often lapsed into procedural thinking and relied heavily on stored procedures. Often I find myself of learning one more feature that makes me re-evaluate how I have written SQL in the past.
Learning about recursive common table expressions made me change the way I queried hierarchial data. Table valued functions had a similar impact particularly when using CROSS APPLY . However sometimes it is just the combination of simple features that produce something interesting.
I was tasked with creating a report searching for people based on a search string that a user supplies. The search string is space separated and all the terms in the search need to be matched. So if a user supplies the search phrase “Jam Wa” then the query must return return a person named “Walker, James” and “James Walker” but not “Brian Walker”.
The solution is presented below. Once I realized that the rvalue of the LIKE operator could be a column expression then it was simple to use a common table expression MatchSet, a table of like clauses. The only trick was ensuring that the number of rows returned joined against MatchSet was equal to the number of rows in MatchSet. This ensured that all terms in the query were matched.
DECLARE @SearchString VARCHAR(50) SET @SearchString = 'Jam Wal' WITH MatchSet(Match) AS ( SELECT '%' + Value + '%' as Match FROM Warehouse.fnSplit(' ', @SearchString) WHERE Value != '' ) SELECT * FROM tblPhysicalUnit WHERE ID IN ( SELECT P.ID FROM tblPhysicalUnit P JOIN MatchSet M ON P.DisplayString LIKE M.Match GROUP BY P.ID HAVING COUNT(*) = (SELECT COUNT(*) FROM MatchSet) ) CREATE FUNCTION Warehouse.fnSplit ( @sep CHAR(1), @s VARCHAR(512) ) RETURNS table AS RETURN ( WITH Pieces(PartNumber, start, stop) AS ( SELECT 1, 1, CHARINDEX(@sep, @s) UNION ALL SELECT PartNumber + 1, stop + 1, CHARINDEX(@sep, @s, stop + 1) FROM Pieces WHERE stop > 0 ) SELECT PartNumber AS ID, SUBSTRING(@s, start, CASE WHEN stop > 0 THEN stop-start ELSE 512 END) AS Value FROM Pieces )
Updated September 12, 2010: Added fnSplit funciton.