Menu
This doesn't return the expected results. Not sure how I can escape the left and right square brackets, so that PATINDEX takes them into account.
Any clues? Many thanks.
SELECT
PATINDEX('%[[SQLSERV]].DBNAME.DBO.[[[0-9a-zA-Z]]]%','ert[SQLSERV].DBNAME.DBO.[Table name]asdadsf')
This should return 3 but it returns 0.
269
To escape square brackets in LIKE you can use another square bracket to escape the original square bracket or use a custom escape character using the ESCAPE keyword in LIKE clause.
Regardless of following a naming convention that avoids using reserved words, Microsoft does add new reserved words. Using brackets allows your code to be upgraded to a new SQL Server version, without first needing to edit Microsoft's newly reserved words out of your client code.
On SQL Server and MS Access, square brackets have a special meaning when used in a query filter. The square brackets are used to specify a set or range of characters, as in "[A-Z]" which would match any single character from 'A' to 'Z'.
Apparently closing brackets don't need to be escaped:
SELECT
PATINDEX('%[[]SQLSERV].DBNAME.DBO.[[][0-9a-zA-Z _-]%','ert[SQLSERV].DBNAME.DBO.[Table name]asdadsf')
the above returns 4.
Also, we can't customise an escape character coupled with PATINDEX as we can do with LIKE.
64
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With