How to escape square brackets in a like clause in sql server

This is a common issue that you find when you try to look for brackets or other wildcard characters using LIKE sentence in Sql Server.


All of these wildcards work on a different way when invoking them into a LIKE sentence, You can check them on the following table.

Wildcard characterDescription
%Any string of zero or more characters.
_ (underscore)Any single character.
[ ]Any single character within the specified range ([a-f]) or set ([abcdef]).
[^]Any single character not within the specified range ([^a-f]) or set ([^abcdef]).

As you can see square brackets are characters reserved for making comparisons on a specific range of letters.

So, If you want to use open square brackets or any other wildcard character in a like clause you should use the ESCAPE keyword in the LIKE predicate.

Datasets

For showing the way on which ESCAPE keyword works on LIKE predicate, we are going to create a table that will contain rows with a different square brackets combinations.

Create Table users(
username varchar(250)
);

INSERT INTO users
VALUES   ('Agent[1]')
        ,('[]DELT')
	,('OCT[5153]]]]')
	,(']OM51253')
	,('DC51]253')
	,('DCD[[[[523')
        ,('DCD[[523')

After creating the table and inserting data into it, we are going to proceed to explore different scenarios.

1. Escaping a single open square bracket.

The first scenario consist into looking for an open bracket ‘[‘. As you can see in the table, multiple rows contains this character.

But when we execute the following query we are not obtaining any results.

For this case we should use ESCAPE keyword into LIKE predicate and then add the ESCAPE character.

In this case we are going to use ‘\’ as the escape character

Example

SELECT * 
FROM users 
WHERE username like '%\[%' ESCAPE '\'

Results

This is going to return all records that contains open bracket on it.

A recommendation for using the escape character is not to use something you want to search in the LIKE predicate.

This is going to return the same result than using ‘/’ as an escape character but if we want to look into the table for a record that contains ‘D’, query is not going to return a single result.

Try not to use an escape character that you are going to search into the Like predicate.

2. Escaping multiple open square brackets.

The second scenario consist in looking for consecutive open brackets ‘[‘. For example if we look into the table for the following characters ‘[[[‘

It seems like we would need to apply the escape character for each open bracket as follows.

Example

SELECT * 
FROM users 
WHERE username like '%\[\[%' ESCAPE '\'

Results

3. Escaping closed square brackets.

The third scenario consist in looking into our table for one or more closed square brackets.

When you look for a single close square bracket you will not need to escape it.

Apparently, only open brackets are reserved wildcards in Sql Server.

For example, when you look for multiple closed brackets, there is no need of escaping it.

4. Conclusion

When dealing with wildcards on Sql Server, you should use ESCAPE keyword into the LIKE predicate, Avoid using an ESCAPE character that you are going to search using the LIKE predicate.


Leave a Reply

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