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
|
1 2 3 4 5 6 7 8 9 10 |
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
|
1 2 3 4 5 |
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
|
1 2 3 4 5 |
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 :-)