What characters need to be escaped in a Sql Server LIKE query

c# sql-server sql-parametrized-query
Starnuto di topo picture Starnuto di topo · Nov 22, 2017 · Viewed 10k times · Source

The LIKE operator in a SQL server query can be very useful to match custom patterns. However sometimes the need raises to escape some characters or substrings from the pattern, such as ampersands '%', underscores '_', square brackets '[' and ']', etc.

Indeed I'm using parametrized queries but it does not solve LIKE case because for example searching for _ would mean "any character".

What is the set of characters that must be considered while escaping such patterns? Can a C# function be provided to perform a safe escape?

Answer

spodger picture spodger · Nov 22, 2017

%, _, [, ], and ^ need to be escaped, and you will need to choose a suitable escape character, i.e. one that you aren't using elsewhere in your LIKE pattern.

Full description here LIKE (Transact-SQL)

I'm sure you could write a function in C# to do that.

Related questions

How to execute a stored procedure within C# program

I want to execute this stored procedure from a C# program. I have written the following stored procedure in a SqlServer query window and saved it as stored1: use master go create procedure dbo.test as DECLARE @command as varchar(1000), @…

Read More
How can I set an SQL Server connection string?

I'm developing a simple C# application, and I'd like to know this: When I connect my application to SQL Server on my PC, I know the connection string (server name, password, etc.), but when I connect it to another PC, …

Read More
Call a stored procedure with parameter in c#

I can do a delete, insert and update in my program and I try to do an insert by call a created stored procedure from my database. This a button insert I make work well. private void btnAdd_Click(object …

Read More