Escaping square brackets in LIKE clause in SQL Server

Problem

When I tried to filter the results returned by a SELECT statement using a LIKE clause with a string containing square brackets, I could not get the expected result. For example, in a sample table column, there is a string value WD[C]97586. But, when I try to apply LIKE ‘WD[C]%’ to the select statement, it is not returning any records.

SELECT * 
   FROM MTB_Table_A 
   WHERE [Name] LIKE 'WD[C]%'
GO

SQL escape square brackets in like clause - Problem

Solution

This is because the square bracket is one among the wildcard characters in the LIKE clause. According to Microsoft Docs, square brackets can be used in LIKE clause to match any single character within the specified range ([b-h]) or set ([ghijk]).

The solution for this is to escape the square bracket. So, how to escape square brackets in LIKE clause? Let’s see.

To escape square brackets in LIKE you can use another square bracket to escape the original square bracket or use a custom escape character using the ESCAPE keyword in LIKE clause. Let’s see this with examples.

Escape using another square bracket

In this method, you just need to escape the opening square bracket ‘[‘ with square brackets ‘[]’. Example:

SELECT * 
   FROM MTB_Table_A 
   WHERE [Name] LIKE 'WD[[]C]%'
GO

NOTE: You do not need to escape the closing square bracket ‘]’ as it does not have any special meaning after escaping the opening square bracket.

Escape using custom escape character

The next option is to use a custom escape character. This method will avoid the confusion of using multiple square brackets as seen above. When using a custom escape character, you have to specify the character with ESCAPE keyword. Example:

SELECT * 
   FROM MTB_Table_A 
   WHERE [Name] LIKE 'WD\[C]%' ESCAPE '\'
GO

In the above example, backslash ‘\’ is the custom escape character.

SQL escape square brackets in like clause - Solution

Reference

More about LIKE clause at Microsoft Docs.