I really like the LIKE operator! (See what I did there?)
I use it on a daily basis. It does have one frustrating shortcoming, however. Unlike the IN operator (where a list is used for comparison), you can only compare one pattern with each LIKE statement.
If you have only a few patterns, this isn’t too much of a problem.
SELECT * FROM tbl_ThatTableOverThere WHERE ThisColumn LIKE '%pattern1%' OR ThisColumn LIKE '%pattern2%' OR ThisColumn LIKE '%pattern3%' -- [...] OR ThisColumn LIKE '%patternX%';
But what if X is a very large number? Hundreds? Thousands? Maybe even millions?
I wrote the code below to allow for multiple patterns to be compared. The patterns are inserted into a table variable (@patterns) and are selected using a fast forward cursor. Unless the number of patterns is truly astronomical, this should suffice.
USE DatabaseName
GO
SET NOCOUNT ON
DECLARE @random INT, @upper INT, @lower INT, @randomTable NVARCHAR(20)
DECLARE @SQL NVARCHAR(MAX)
DECLARE @whereClause NVARCHAR(MAX)
DECLARE @sourceTable NVARCHAR(255)
DECLARE @columnName NVARCHAR(255)
DECLARE @pattern NVARCHAR(255)
DECLARE @patterns TABLE (pattern NVARCHAR(255) NOT NULL)
DECLARE @deleteTempTable NVARCHAR(MAX)
/* These variables should remain unchanged unless the name of the
temporary table needs to have different text or range of random numbers
for the suffix of the temp table.
[Reference: https://stackoverflow.com/questions/14756780/creating-a-temporary-table-name-with-a-randomly-generated-number] */
SET @lower = 1
SET @upper = 9999999
SELECT @random = ROUND(((@upper - @lower) * RAND() + @lower),0)
SELECT @randomTable = '##search'+CAST(@random AS NVARCHAR(20))+''
-- Name of source table holding data for comparison
SET @sourceTable = 'tbl_Name'
-- Column that is being compared with patterns using LIKE operator
SET @columnName = 'ColumnName'
-- Additional WHERE clause may be specified here
SET @whereClause = '' -- e.g., 'AND AnyColumnName LIKE ''%b%'''
-- Insert patterns into table variable below
INSERT INTO @patterns VALUES
('%pattern1%')
-- ,('%pattern2%')
-- ,(%pattern3%) and so on
-- Deletes temp table if it already exists
SET @deleteTempTable = 'IF object_id(''tempdb..' + @randomTable + ''') IS NOT NULL DROP TABLE ' + @randomTable
EXEC(@deleteTempTable)
-- This line creates the temporary table using the same schema as the source table without inserting data.
-- The UNION ALL operator prevents the IDENTITY property from being applied to any of the columns in the temp table.
-- [Reference: https://dba.stackexchange.com/questions/916/how-do-i-copy-a-table-with-select-into-but-ignore-the-identity-property]
EXEC('SELECT TOP 0 * INTO ' + @randomTable + ' FROM ' + @sourceTable + ' UNION ALL SELECT TOP 0 * FROM ' + @sourceTable)
SET @SQL = 'INSERT INTO ' + @randomTable + CHAR(13) + 'SELECT TOP 0 * FROM ' + @sourceTable
DECLARE patternsCursor CURSOR FAST_FORWARD FOR
SELECT pattern FROM @patterns
OPEN patternsCursor
FETCH NEXT FROM patternsCursor INTO @pattern
WHILE @@FETCH_STATUS = 0
BEGIN
IF LEN(@pattern) > 0
BEGIN
--Note: UNION operator (as opposed to UNION ALL) is used here to prevent duplicate rows from being inserted into table variable
--[Reference: https://www.techonthenet.com/sql/union_all.php]
SET @SQL = @SQL + CHAR(13) + 'UNION' + CHAR(13) + 'SELECT * FROM ' + @sourceTable + CHAR(13) + 'WHERE ' + @columnName + ' LIKE ''' + @pattern + ''''
IF LEN(@whereClause) > 0 SET @SQL = @SQL + CHAR(13) + @whereClause
END
FETCH NEXT FROM patternsCursor INTO @pattern
END
CLOSE patternsCursor
DEALLOCATE patternsCursor
SET @SQL = @SQL + ';'
-- Prints final SQL statement on Results tab
SELECT @SQL
SET NOCOUNT OFF
-- Executes SQL Statement showing number of rows inserted on Messages tab
EXEC(@SQL)
SET NOCOUNT ON
-- Executes SQL Statement showing rows in Results tab
EXEC('SELECT * FROM ' + @randomTable + ';')
-- Deletes temp table
EXEC(@deleteTempTable)
