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.