Dynamically build a multi OR with LIKE query for SQL Server
By: Eli Leiba | Updated: 2016-11-03 | Comments (1) | Related: More > T-SQL
You need to write a query that creates a bunch of LIKE statements for an unknown number of values, such as the code sample below:
SELECT * FROM T WHERE T.C LIKE '%S1%' or T.C LIKE '%S1%' or T.C LIKE '%S2%' or T.C LIKE '%S3%' or T.C LIKE ... or T.C LIKE ... or T.C LIKE '%Sn%'
You want to accomplish this with a list of strings, in any length, without having to write a different query each time. How can this be accomplished using T-SQL?
The solution consists of two objects. These should be built in the appropriate user database.
- A function that returns a table of values
- A stored procedure that generates the query code dynamically and executes the query
The function is just a simple process to break our multi-value string into a table where each value is returned as a row.
CREATE FUNCTION dbo.String_Split ( @Str NVARCHAR(MAX) ,@Delim VARCHAR(1) ) RETURNS @RtnValue TABLE ( nr INT IDENTITY(1, 1) ,token NVARCHAR(MAX) ) AS BEGIN DECLARE @Idx INT DECLARE @FoundIdx INT SET @Idx = 1 SET @FoundIdx = CHARINDEX(@Delim, @Str) WHILE (@FoundIdx > 0) BEGIN INSERT INTO @RtnValue (token) SELECT LTRIM(RTRIM(SUBSTRING(@Str, 1, @FoundIdx - 1))) SET @Str = SUBSTRING(@Str, @FoundIdx + 1, len(@str) - @FoundIdx) SET @Idx = @Idx + 1 SET @FoundIdx = CHARINDEX(@Delim, @Str) END INSERT INTO @RtnValue (token) SELECT LTRIM(RTRIM(@Str)) RETURN END GO
The second object is a stored procedure that gets the table name, column name and string list parameters and uses the above function to build and execute a dynamically built query.
CREATE PROC usp_ORWithLikeConds ( @tableName VARCHAR(50) ,@columnName VARCHAR(50) ,@LikePatterns VARCHAR(300) ) AS BEGIN DECLARE @tsql VARCHAR(500) SET NOCOUNT ON SET @tsql = CONCAT ( 'SELECT * FROM ' ,@tableName ,' as T Where Exists (Select * from dbo.String_split (' ,'''' ,@LikePatterns ,'''' ,',' ,'''' ,',' ,'''' ,') as P ' ,'Where T.' ,@columnName ,' LIKE ' ,'''' ,'%' ,'''' ,'+P.token+' ,'''' ,'%' ,'''' ,')' ) PRINT @tsql EXEC (@tsql) END GO
Using the Function and Stored Procedure
Here is an example of how to use the above objects.
We will run the stored procedure to find all Adddress1 columns that contain patterns like "oak" and "water" in the Address table. I am using only two LikePatterns, but this can be extended to as many as you need.
exec usp_ORWithLikeConds @tableName='Address',@columnName='Address1',@LikePatterns='oak,water' go
The stored procedure builds the query as follows:
SELECT * FROM Address as T Where Exists (Select * from dbo.String_split ('oak,water',',') as P Where T.Address1 LIKE '%'+P.token+'%')
and also runs and the results are as follows. (for clarity, I only displayed the Address1 column even though all columns are returned with the query).
Address1 ----------- 8501 White Oaks Road 1162 Charter Oaks Ct. 12217 W Watertown Plank Road 12217 W. Watertown PlankRd Po Box 147/500 N Oak 4450 Oakwood Lane 1501 s. oak st 2525 E. Oakton Street 9480 Watertown Plank Road 6221 oakwood ave. north
- In SQL 2016 there is a new String_Split function which does the same as the above function.
- The procedure and function were tested with SQL 2012 and 2014 Developer Edition.
- The procedure and function should be compiled in the appropriate user application database.
Last Updated: 2016-11-03
About the author
View all my tips