Press enter to see results or esc to cancel.

How To Escape Square Brackets in SQL

I was writing a batch replace query in SqlServer today and ran into an interesting issue. How do I string replace a square bracket [ in a text field using the LIKE operator?

After a lot of searching I found that I can escape a literal bracket character using [[] when using the LIKE operator.

So my query can look like something like this:

WHERE my_column LIKE '%[[]some text%'

Source: LIKE (Transact-SQL), under the section “Using Wildcard Characters As Literals.”

Why is this an issue? Actually, it’s because square brackets can be used in combination with a LIKE operator to match a pattern. For example, WHERE code LIKE ‘[A-C]ol’ will give you codes ending with ol and starting with any single character between A and C, so the query will match Aol, Bol, Col.

Since the LIKE operator uses [ bracket for pattern matching, you cannot match for the literal character [ without running into an issue, and hence you need to escape the [ character as [[] as explained above for your literal match to work.

Comments

Leave a Comment