x

String search like that contains [

When I am searching a field that contains the "[" charachter using the like search option no records are returned even though the field contains string that include the "[" character.
e.g. select CustomerDescription from customer where CustomerDescription like '%[j fred muggs]%'
There is a record where the CustomerDescription is "my friend is [j fred muggs] a nice guy". Is the "[" character not searchable and if so are there other similar charachters?
more ▼

asked Nov 02 '10 at 08:39 AM in Default

ChasOest gravatar image

ChasOest
11 1 1 1

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

4 answers: sort voted first
you are searching for a 'special' character so you need to tell the SQL that you actually want to look for that rather than use its 'special powers'. Details here http://msdn.microsoft.com/en-us/library/ms179859.aspx
more ▼

answered Nov 02 '10 at 09:04 AM

Fatherjack gravatar image

Fatherjack ♦♦
41.3k 73 77 107

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

Oddly enough you can escape with brackets like this:

declare @test char(10)

SET @Test = '123[x'

IF @test LIKE '%[[]%'
    PRINT 'OK'
ELSE
    PRINT 'missed it'

[Edit] Another, possibly better way is to use the ESCAPE command like this:

declare @test char(10)

SET @Test = '123[x'

IF @test LIKE '%\[%' ESCAPE '\'  
    PRINT 'OK'
ELSE
    PRINT 'missed it'
more ▼

answered Nov 02 '10 at 09:02 AM

Mark gravatar image

Mark
2.6k 21 25 27

(comments are locked)
10|1200 characters needed characters left
select CustomerDescription from customer where CustomerDescription like '%]%'
select CustomerDescription from customer where CustomerDescription like '%[[%muggs]%'
more ▼

answered Nov 02 '10 at 09:44 AM

Leo gravatar image

Leo
1.6k 51 56 58

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

[ character is a special character used in search strings if you use

select CustomerDescription from customer where CustomerDescription like '%[cp]ut%'

it 'll be interpreted as you wish to search customer descriptions that contains 'cut' or 'put' AND

select CustomerDescription from customer where CustomerDescription like '%[c-r]ash%'

'll be interpreted as you wish to search customer descriptions that contains a word ends with 'ash' and begins with any character ranges from c to r in alphabetical order

the best way to overcome this problem is to use ESCAPE clause as follows

 select CustomerDescription from customer where CustomerDescription like '%![j fred muggs]%' ESCAPE '!'
this will tell SQL SERVER that the character that comes immediately after ! sign will interpret as it is
more ▼

answered Nov 03 '10 at 05:02 AM

Baligh gravatar image

Baligh
21

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

x33
x18

asked: Nov 02 '10 at 08:39 AM

Seen: 1533 times

Last Updated: Nov 02 '10 at 09:01 AM