Handling Square Brackets ([]) in MS-SQL LIKE Searches

Handling Square Brackets ([]) in MS-SQL LIKE Searches

In MS-SQL, square brackets ([]) are treated as special wildcard characters in LIKE clauses, similar to regular expressions. This can cause issues when searching for literal square brackets in data, such as [something].

To escape square brackets and treat them as literal characters, you need to use specific escaping techniques.

---

Problem

• Square brackets are interpreted as wildcard characters.
• Searching for data like [something] directly with LIKE will not match the expected results.

Solution

To search for strings containing square brackets, you need to escape them by replacing [ with [[]. Here’s an example:

DECLARE @PARAM AS NVARCHAR(20);
SET @PARAM = '[some%';
SET @PARAM = REPLACE(@PARAM, '[', '[[]'); -- Escaping '['
SELECT * 
FROM TABLE
WHERE COL LIKE @PARAM;

Explanation:

1. SET @PARAM = '[some%':
   The input string that contains a square bracket ([).

2. SET @PARAM = REPLACE(@PARAM, '[', '[[]'):
   Replaces all occurrences of [ with [[], effectively escaping the square bracket.

3. WHERE COL LIKE @PARAM:
   Searches for rows in TABLE where the COL column matches the modified @PARAM.

---

Important Notes

• Escaping is only required for the opening square bracket ([). Closing brackets (]) do not require escaping in MS-SQL LIKE.
• The solution works with patterns containing other wildcards like % and _.

---

Wildcards in MS-SQL LIKE

• %: Matches any sequence of characters.
• _: Matches a single character.
• []: Matches any single character within the brackets.
• [^]: Matches any single character not within the brackets.

---

References

For further information, refer to the official MSDN documentation:
MS-SQL Wildcards

By following these steps, you can handle square brackets in LIKE searches effectively, ensuring accurate query results.