T-SQL: How to Search for % in String (% and LIKE)

In one of my web projects (ASP.NET and SQL Server) I have a screen with top 50 customers. To facilitate the client, I put few textboxes and give them the chance to filter the customers, until they reach the one that they need. In another case I use a textbox with auto complete to allow the client to add existing customer to a group. I needed to search for a group with “%” in its name. As you know in T-SQL, the LIKE operator uses % as a wildcard and when we search for %, the LIKE operator returns everything.

I was searching for an answer how to search for % and I couldn’t find one in 20 minutes. I decided that this is very simple and waisting 20 minutes is too much. That’s why I got a solution in 5 minutes.

Create and populate the test table

DROP TABLE IF EXISTS #Table1;

SELECT 'A toy with 10 % rebate' AS Column1
INTO #Table1
UNION ALL SELECT 'Street Address'
UNION ALL SELECT 'Customer with 14% abscense'
UNION ALL SELECT 'Another Unknown Address';

SELECT *
FROM #Table1;

DROP TABLE IF EXISTS #Table1;

SELECT 'A toy with 10 % rebate' AS Column1

INTO #Table1

UNION ALL SELECT 'Street Address'

UNION ALL SELECT 'Customer with 14% abscense'

UNION ALL SELECT 'Another Unknown Address';

SELECT *

FROM #Table1;

The straight forward using of % doesn’t work and the query below returns all the rows in the table

DECLARE @Pattern NVARCHAR(128) = N'%'

SELECT *
FROM #Table1
WHERE Column1 LIKE N'%'

DECLARE @Pattern NVARCHAR(128) = N'%'

SELECT *

FROM #Table1

WHERE Column1 LIKE N'%'

By replacing % with “\%” and add the left slash like escape character fixes the query

DECLARE @Pattern NVARCHAR(128) = N'%' -- '' --> All Values

SELECT *
FROM #Table1
WHERE Column1 LIKE N'%' + REPLACE(@Pattern, '%', '\%') + N'%' ESCAPE '\';

DECLARE @Pattern NVARCHAR(128) = N'%' -- '' --> All Values

SELECT *

FROM #Table1

WHERE Column1 LIKE N'%' + REPLACE(@Pattern, '%', '\%') + N'%' ESCAPE '\';

In my project, empty textbox means “no filter”, so the above solution is perfectly acceptable.

Warning: LIKE is an expensive operator. In this case I use it to filter “structure” or “dimension” table (Customer). If we need to filter a “data” or “fact” table, we would consider another approach.

Keep it simple :-)