Search code examples
sql-servercharactersymbolspatindex

mssql patindex for ] symbol

How to specify ] symbol inside character class (MS SQL SERVER PATINDEX function)?

'%["[]%' - for starting bracket - it works
'%["]]%' - for ending - it does not
Solution

  • Looks like there is no way to properly escape closing bracket (]) in PATINDEX. ] alone can be written verbatim, but cannot be included in the character set.

    However, according to this DBA.SE question, there are some workaround (see the linked article for the full example):

    1. Specify character range that contains ]. (note that this will match unwanted characters)
    PATINDEX('%[[-^{}:,]%' COLLATE Latin1_General_BIN2, MyJSONString)
    1. Apply REPLACE before match.
    PATINDEX('%[[' + CHAR(174) + '@]%', REPLACE(@test,']',CHAR(174)))
    1. Use PATINDEX twice: one for ], and the other for the rest of characters.
    (NULLIF(PATINDEX('%[[{}:,]%', d.ResponseJSON), 0), NULLIF(PATINDEX('%]%', d.ResponseJSON), 0)))