Unable to match square bracket [ character in SQL

Question

I am trying to select records that start with this string pattern 'Dear [User Name]'. The following where clause:

where Message like 'Dear [UserName]%'

returns nothing.
What is the problem and what is the solution?

Note 1: the field [Message] DOES start with this text.
Note 2: the type of field [Message] is text, but the same problme happens with varchar fields.

There is a question already posted. See this. http://stackoverflow.com/questions/8984380/sql-server-need-to-escape — Jayy, Feb 21 '12 at 07:07
I'm using SQL Server 2008 R2. And yes, this question is a duplicate of http://stackoverflow.com/questions/8984380/sql-server-need-to-escape, so I will close it. Thank you. — Marwan, Feb 21 '12 at 07:44

score 2 · Answer 1 · answered Feb 21 '12 at 07:02

The [] is a wild card character matching operator which "Matches any single character within the specified range or set that is specified between the brackets." http://msdn.microsoft.com/en-us/library/ms179884.aspx

To get a correct match you can write:

where Message like 'Dear [[]User Name]%'

score 2 · Accepted Answer · answered Feb 21 '12 at 07:48

2

try this

 where Message like 'Dear \[User Name\]' escape '\';

answered Feb 21 '12 at 07:48

Vikram

8,235
33
47

Great. Thanks. Even if you do not escape the ] it still works. – Marwan Feb 21 '12 at 08:07

Unable to match square bracket [ character in SQL

2 Answers2