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

avatar image

Guy
46 2 2 5

(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

avatar image

Matt Whitfield ♦♦
29.5k 62 66 88

(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

avatar image

Melvyn Harbour 1 ♦♦
1.4k 19 40 26

(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.

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:

x150
x23

asked: Oct 28, 2009 at 11:44 AM

Seen: 14895 times

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

Copyright 2016 Redgate Software. Privacy Policy