August 25, 2009 at 9:46 pm
Comments posted to this topic are about the item LIKEable Brackets
August 26, 2009 at 6:26 am
Clever question, not sure why/how I'd ever use the concept though. Good trivia?
August 26, 2009 at 6:33 am
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?
August 26, 2009 at 6:49 am
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.
August 26, 2009 at 7:47 am
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
August 26, 2009 at 8:39 am
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
August 26, 2009 at 9:12 am
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.
August 26, 2009 at 12:43 pm
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) [[b]][][/b], 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!
August 26, 2009 at 12:44 pm
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. 😉
August 26, 2009 at 12:46 pm
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?
August 26, 2009 at 1:01 pm
Nice topic. I have had to use this as well for searching special characters of this type due to the nature in which the application and database were developed which i inherited. Loads of fun 😉
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 26, 2009 at 1:02 pm
Thanks, Hugo, for the practical applications. Didn't think about something like searching the newsgroups.
August 26, 2009 at 1:45 pm
Steve Jones - Editor (8/26/2009)
Thanks, Hugo, for the practical applications. Didn't think about something like searching the newsgroups.
Yeah, I once had to write code to manipulate some strings that might contain one of each of these patterns: [1xxx|yyy1], and [2xxx|yyy2]. One condition determined whether the first pattern should be replaced by either xxx or yyy, and another condition had a similar function for the second pattern. And both patterns can be present in a single string. Had loads of fun writing the LIKE and PATINDEX functions for that one! :w00t:
October 29, 2009 at 5:19 am
Hugo Kornelis (8/26/2009)
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!
Thanks. I made exactly the same incorrect analysis of 2, but decided to read the comments before working out why - so your description of why 2 doesn't work has saved me some brain strain.
Tom
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply