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