escape square brackets in PATINDEX with SQL Server

Question

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.

score 9 · Answer 1 · answered Sep 07 '15 at 11:23

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.

1 Answers1