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)
You must log in to post a comment.