Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

LIKEable Brackets Expand / Collapse
Author
Message
Posted Tuesday, August 25, 2009 9:46 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, September 22, 2014 11:18 AM
Points: 305, 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
Post #777232
Posted Wednesday, August 26, 2009 6:26 AM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Wednesday, January 2, 2013 12:15 PM
Points: 1,443, Visits: 711
Clever question, not sure why/how I'd ever use the concept though. Good trivia?
Post #777420
Posted Wednesday, August 26, 2009 6:33 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 12:24 PM
Points: 3,384, Visits: 2,017
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?
Post #777423
Posted Wednesday, August 26, 2009 6:49 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 10:17 AM
Points: 31,278, Visits: 15,731
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
Post #777433
Posted Wednesday, August 26, 2009 7:47 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 4:51 PM
Points: 2,394, Visits: 18,021
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
Post #777493
Posted Wednesday, August 26, 2009 8:39 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 9:59 AM
Points: 229, Visits: 252
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
Post #777552
Posted Wednesday, August 26, 2009 9:12 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, July 17, 2014 10:56 AM
Points: 3,924, Visits: 1,607
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.
Post #777595
Posted Wednesday, August 26, 2009 12:43 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 3:56 PM
Points: 6,086, Visits: 8,354
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
Post #777758
Posted Wednesday, August 26, 2009 12:44 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 3:56 PM
Points: 6,086, Visits: 8,354
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
Post #777759
Posted Wednesday, August 26, 2009 12:46 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 3:56 PM
Points: 6,086, Visits: 8,354
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
Post #777760
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse