Searching for text with square brackets

Question

I have a text field in SQL Server 2005 and it has text like this:

'...this is a [word] that we know...'

I'm trying to search for all records that have '[word]' in that field. So far this works to find '[word':

SELECT * FROM TableName where FieldName like '%[[]word%'

but this does NOT work to find '[word]':

SELECT * FROM TableName where FieldName like '%[[]word[]]%'

What am I doing wrong in the second select statement?

Answer 1

0

There is an MSDN Topic that explains it.

You want

SELECT * FROM TableName where FieldName like '%[[]word]%'

answered Oct 28 '09 at 11:56 AM

Matt Whitfield ♦♦
29.2k ● 56 ● 63 ● 87

Yes, indeed, you're right as well. I just get used to using 'normal' escaping from every other Regex language out there!

Oct 28 '09 at 12:07 PM Melvyn Harbour 1 ♦♦

I'm not sure how the original inventors of the SQL-style 'regex' got anything done - they must have been smoking something pretty strong!

Oct 28 '09 at 12:14 PM Matt Whitfield ♦♦

Thanks - would have also given you +1 but only have 13 rep at time of posting this.

Dec 01 '09 at 11:27 AM Guy

(comments are locked)

Answer 2

0

Have a read of http://msdn.microsoft.com/en-us/library/ms179859.aspx

What's happening when you're using the square brackets is that you're defining character classes. So in the second example, it uses the outer set of square brackets to mark out a character class that is: []word[] (which is equivalent to dorw[]).

What you want is to use an ESCAPE clause:

SELECT * FROM TableName WHERE FieldName LIKE '%![word!]%' ESCAPE '!'

answered Oct 28 '09 at 12:00 PM

Melvyn Harbour 1 ♦♦
1.4k ● 17 ● 20 ● 22

Thanks - works well - would have given +1 but only have 13 rep right now.

Dec 01 '09 at 11:27 AM Guy

(comments are locked)

Searching for text with square brackets

Follow this question

Related Questions