05 Sep 2010Multi Value Like Parameters In Sql
layout: post title: Multi-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.
Updated September 12, 2010: Added fnSplit funciton.