x

Searching for text with square brackets

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?

more ▼

asked Oct 28, 2009 at 11:44 AM in Default

Guy gravatar image

Guy
46 2 2 3

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

There is an MSDN Topic that explains it.

You want

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

answered Oct 28, 2009 at 11:56 AM

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.5k 61 65 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, 2009 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, 2009 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, 2009 at 11:27 AM Guy
(comments are locked)
10|1200 characters needed characters left

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 '!' 
more ▼

answered Oct 28, 2009 at 12:00 PM

Melvyn Harbour 1 gravatar image

Melvyn Harbour 1 ♦♦
1.4k 18 20 22

Thanks - works well - would have given +1 but only have 13 rep right now.
Dec 01, 2009 at 11:27 AM Guy
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x110
x22

asked: Oct 28, 2009 at 11:44 AM

Seen: 8960 times

Last Updated: Oct 28, 2009 at 12:08 PM