CODEWITHSUNDEEP
sqlsql-server-2005searchsql-like
Singleton
Singleton

Reputation: 3679

Like query not working when using [ or ] in like

Need to ask about strange behaviour of SQL Server 2005.

What I need is to find records that contain ']' or '[' in my column

I have a data in column like this

'6b51c65b-5773-415a-aec9-8482c404faef_megas_xlr[1].jpg'

When I run this query

select * from Theme  where BackgroundImageUrl like '%[%'

then no records comes up, but running this below query

select * from Theme  where BackgroundImageUrl like '%]%'

boils following output

'6b51c65b-5773-415a-aec9-8482c404faef_megas_xlr[1].jpg'

What's wrong in that query? What do I need to do in order to search records having closing or starting brackets []?

EDIT..

For more clarification, here is my test data. A[1].jpg B[1.jpg C1].jpg

And below are results that I got after running following combination

A select BackgroundImageUrl from Theme where BackgroundImageUrl like '%[%'

No Results

B select BackgroundImageUrl from Theme where BackgroundImageUrl like '%]%'

A[1].jpg C1].jpg

C select BackgroundImageUrl from Theme where BackgroundImageUrl like '%[ [ ]%'

A[1].jpg B[1.jpg

D select BackgroundImageUrl from Theme where BackgroundImageUrl like '%[ ] ]%'

No Results

What I need is that, Case 1 : I need all records that contains '['

Case2 : I need all records that contains ']'

Query C fulfills my case 1 but I m not able to get result for my case 2

Upvotes: 2

Views: 5092

Answers (5)

Singleton
Singleton

Reputation: 3679

Well i just discovered my answer.

select * from Theme  where BackgroundImageUrl like  '%\]%' ESCAPE '\'

and

select * from Theme  where BackgroundImageUrl like  '%\[%' ESCAPE '\'

Upvotes: 1

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

From here: http://msdn.microsoft.com/en-us/library/ms179859.aspx

Get the rows containing [ or ] like this.

declare @T table (S varchar(100))

insert into @T values ('A[1].jpg')
insert into @T values ('B[1.jpg')
insert into @T values ('C1].jpg')

select *
from @T
where S like '%[[]%' or
      S like '%]%'

Result;

S
---------
A[1].jpg
B[1.jpg
C1].jpg

Upvotes: 0

Andrew Bickerton
Andrew Bickerton

Reputation: 478

[] are an explicit part of the wild card search:

Wildcard character Description Example
% Any string of zero or more characters. WHERE title LIKE '%computer%' finds all book titles with the word 'computer' anywhere in the book title.

_ (underscore) Any single character. WHERE au_fname LIKE '_ean' finds all four-letter first names that end with ean (Dean, Sean, and so on).

[ ] Any single character within the specified range ([a-f]) or set ([abcdef]). WHERE au_lname LIKE '[C-P]arsen' finds author last names ending with arsen and starting with any single character between C and P, for example Carsen, Larsen, Karsen, and so on. In range searches, the characters included in the range may vary depending on the sorting rules of the collation.

[^] Any single character not within the specified range ([^a-f]) or set ([^abcdef]). WHERE au_lname LIKE 'de[^l]%' all author last names starting with de and where the following letter is not l.

you need to apply an escape clause to have SQL Server evaluate [] as what you are searching for:

match_expression [ NOT ] LIKE pattern [ ESCAPE escape_character ]

*escape_character*
Is a character that is put in front of a wildcard character to indicate that the wildcard should be interpreted as a regular character and not as a wildcard. escape_character is a character expression that has no default and must evaluate to only one character.

or you can use: Using Wildcard Characters As Literals You can use the wildcard pattern matching characters as literal characters. To use a wildcard character as a literal character, enclose the wildcard character in brackets. The following table shows several examples of using the LIKE keyword and the [ ] wildcard characters.

Symbol           Meaning  
---------------  --------------------
LIKE '5[%]'      5%
LIKE '[_]n'      _n
LIKE '[a-cdf]'   a, b, c, d, or f
LIKE '[-acdf]'   -, a, c, d, or f
LIKE '[ [ ]'     [
LIKE ']'         ]
LIKE 'abc[_]d%'  abc_d and abc_de
LIKE 'abc[def]'  abcd, abce, and abcf

Upvotes: 3

gbn
gbn

Reputation: 432230

You have to escape the opening bracket:

LIKE '%[[]%

but you don't have to escape the closing one (as you noted)

The rest of the "sometimes" logic is up to you. Or a new, complete, question. We've answered this one.

Upvotes: 0

Jacob
Jacob

Reputation: 43219

LIKE (Transact-SQL)

[] is used to identify ranges in a LIKE-clause.

The documentation linked above has following solution:

Using Wildcard Characters As Literals

You can use the wildcard pattern matching characters as literal characters. To use a wildcard character as a literal character, enclose the wildcard character in brackets. The following table shows several examples of using the LIKE keyword and the [ ] wildcard characters.

Symbol       | Meaning
LIKE '[ [ ]' | [
LIKE ']'     | ]

Your query would have to look like this:

select * from Theme  where BackgroundImageUrl like '%[ [ ]%'

Edit: Assuming you only want files that either have a [ OR a ], but not both:

select * from Theme  where (BackgroundImageURL like '%]%' AND BackgroundImageURL  not like '%[[]%')  or (BackgroundImageURL  like '%[[]%' AND BackgroundImageURL  not like '%]%')

Upvotes: 0

Related Questions