LIKEable Brackets

  • Comments posted to this topic are about the item LIKEable Brackets

  • Clever question, not sure why/how I'd ever use the concept though. Good trivia?

  • 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?

  • 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.

  • 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

  • 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

  • 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.

  • 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!


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • 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/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • 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/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • 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

  • Thanks, Hugo, for the practical applications. Didn't think about something like searching the newsgroups.

  • 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:


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • 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