Comparing Multiple Patterns with LIKE Operator

pic of Facebook Like button

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)

Using MAX() in a SQL Subquery

database diagram showing table relationships

One pro bono project I’m working on is improving a school website where parents can sign up for classes, view students’ grades, etc. One of the problems with the website was that the list of user accounts, which includes all parents and teachers, includes parents from previous years who no longer have students there.

The query for pulling this information was very simple:

SELECT * FROM UserAccounts ORDER BY lastName ASC

This query pulled all users and ordered them only by last name. The data was being dumped into an ASP.NET GridView with column sorting, and there were only a couple hundred people there, so it wasn’t completely unmanageable. However, dealing with all the parents who no longer have kids there did make visual searches more difficult. The request was to bring all current parents to the top of the list so that other admins don’t have to search through pages of people who no longer attend.

A quick fix for this would be to sort the list based on last academic year attended. We’ll do this by employing the MAX() function.

In addition to the UserAccounts table, there are other tables called “students” (which includes all current and past students) and “schedule” (which holds student schedules). In the schedule table, a column called “s_year” that holds the academic year (in the format of “2017-18”). We can join these tables based on user account IDs in the UserAccount table, the parentID column in the students table, and the student id (s_id) column in the schedule table.

One more thing: Since this list also includes people who have never had students there, the revised query will have to take that into account.

database diagram showing table relationships

The below query will not only solve this problem, but will also sort by first and last name. Users with no academic year (a NULL value) will be at the bottom of the list.

SELECT DISTINCT ua.*, sc.s_year FROM UserAccounts ua 
LEFT OUTER JOIN students s ON s.parentID = ua.ID 
LEFT OUTER JOIN schedule sc ON sc.s_id = s.ID 
WHERE sc.s_year IS NULL 
OR sc.s_year = (
   SELECT MAX(sc2.s_year) FROM schedule sc2 WHERE sc2.s_id = s.ID
) 
ORDER BY sc.s_year DESC, ua.lastName, ua.firstName

The only remaining thing to do is to add the year column to the GridView and make sure that sorting is enabled.