SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


LIKEable Brackets


LIKEable Brackets

Author
Message
Jesse McLain
Jesse McLain
Mr or Mrs. 500
Mr or Mrs. 500 (565 reputation)Mr or Mrs. 500 (565 reputation)Mr or Mrs. 500 (565 reputation)Mr or Mrs. 500 (565 reputation)Mr or Mrs. 500 (565 reputation)Mr or Mrs. 500 (565 reputation)Mr or Mrs. 500 (565 reputation)Mr or Mrs. 500 (565 reputation)

Group: General Forum Members
Points: 565 Visits: 567
Comments posted to this topic are about the item LIKEable Brackets

Jesse McLain
jesse@jessemclain.com
www.jessemclain.com
www.jessesql.blogspot.com
SuperDBA-207096
SuperDBA-207096
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2969 Visits: 711
Clever question, not sure why/how I'd ever use the concept though. Good trivia?
Ken Wymore
Ken Wymore
SSCertifiable
SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)

Group: General Forum Members
Points: 6575 Visits: 2396
I am guessing you would use it if you had some sort of program code or oddly formatted text inserted into a table and each string started with either [] or [!] or something else and you wanted to return just those strings containing []? Possibly would be used in a find and replace scenario?
Steve Jones
Steve Jones
SSC Guru
SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)

Group: Administrators
Points: 145301 Visits: 19425
I'm sure there's a use, but not a common one. Certainly I didn't quite get it.

I think half the questions we ask sometimes are trivia, or tightly focused, but it's hard to ask general questions in this format.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Chad Crawford
 Chad Crawford
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4345 Visits: 18732
I think this question is valuable beyond just the trivia for several reasons:
1. Be careful when searching for brackets using like - they are a special character just like the percent and underscore (I had forgotten about this)
2. Remember that you can specify an escape character to search for one of the wildcard characters
3. If you do have some of the wildcard characters in your data, test carefully to make sure you can use LIKE like you want to (I would have thought that 2 would have shown up).

Chad
DBA Cabuloso
DBA Cabuloso
Mr or Mrs. 500
Mr or Mrs. 500 (546 reputation)Mr or Mrs. 500 (546 reputation)Mr or Mrs. 500 (546 reputation)Mr or Mrs. 500 (546 reputation)Mr or Mrs. 500 (546 reputation)Mr or Mrs. 500 (546 reputation)Mr or Mrs. 500 (546 reputation)Mr or Mrs. 500 (546 reputation)

Group: General Forum Members
Points: 546 Visits: 274
Which is the exact meaning of ESCAPE?
I thought it was to escape a wildcard that we would like to search. But the item didn't have any exclamation in the table, but even though the SQL returned the number 1.

Can someone explain why?

________________
DBA Cabuloso
Lucas Benevides
SanjayAttray
SanjayAttray
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5677 Visits: 1619
Mark Horninger (8/26/2009)
Clever question, not sure why/how I'd ever use the concept though. Good trivia?


Exactly. Good Trivia. But never came across a procedure which uses it, neither I ever wrote any script using escape.

SQL DBA.
Hugo Kornelis
Hugo Kornelis
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18709 Visits: 12426
Jesse McLain
When I was researching this, I was quite surprised to see #3 appear in the output.


I'm not. When you need to search for strings that include characters such as % or [, you have to escape them, and there are two ways to do that: by using the ESCAPE clause (but that requires you to be 100% positive that the chosenn escape character is never included in the search string), or by enclosing the special character in a [] pair. We all know that [abc] matches one character that can be either a, b, or c. So likewise, [a] matches one character that can be only a, and [[] or [%] match one character that can be only [ or %.

I missed my point by messing up the second pattern. When looking at the tail end of the pattern, I saw []] and parsed this as []] (the boldface indicates how I matched up closig and ending brackets - like how most typical humans would do it), instead of []] (what the computer apparently does). So the entire pattern string becomes (again, with matching begin and end brackets in bold) [[][]], translating to a single character which has to be [, followed by a single character which may not be any character at all (!), and then a single ] character.

Oh well, missed a point on a question designed to test something I knew, but learned an interesting thing - humans are still too smart for computers!


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Hugo Kornelis
Hugo Kornelis
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18709 Visits: 12426
Mark Horninger (8/26/2009)
Clever question, not sure why/how I'd ever use the concept though. Good trivia?


If you ever need to search a database of newsgroup messages, you'll be delighted to know how to properly search for messages that are marked "off topic" by using the common [OT] prefix in the message title. ;-)


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Hugo Kornelis
Hugo Kornelis
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18709 Visits: 12426
DBA Cabuloso (8/26/2009)
Which is the exact meaning of ESCAPE?
I thought it was to escape a wildcard that we would like to search. But the item didn't have any exclamation in the table, but even though the SQL returned the number 1.

Can someone explain why?


Books Online, perhaps?


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search