Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Dynamically build a multi OR with LIKE query for SQL Server


By:   |   Updated: 2016-11-03   |   Comments (1)   |   Related: More > T-SQL

Problem

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?

Solution

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

Function

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

Stored Procedure

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
Next Steps
  • 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


get scripts

next tip button



About the author
MSSQLTips author Eli Leiba Eli Leiba is a senior application DBA, a teacher and a senior database consultant with 19 years of RDBMS experience.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Friday, November 04, 2016 - 5:23:23 AM - Mikael Eriksson Back To Top

Why do you use the string split function in the dynamic SQL? Would it not be better to build the dynamic where clause using the string split and not use the UDF in the actual query?


Learn more about SQL Server tools