question

ChasOest avatar image
ChasOest asked

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?
stringsearch
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Mark avatar image
Mark answered
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'
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Fatherjack avatar image
Fatherjack answered
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
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Leo avatar image
Leo answered
select CustomerDescription from customer where CustomerDescription like '%]%'
select CustomerDescription from customer where CustomerDescription like '%[[%muggs]%'
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Baligh avatar image
Baligh answered
[ 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
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.