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

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

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

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 :-)

Leave a comment

Your email address will not be published. Required fields are marked *