In the below code example, all results should return 7.
Those with aliases beginning X however, do not.
select
--where matches
patindex('%-%' ,'111111-11') dash --not a special character, so works without escaping
,patindex('%[%' ,'111111[11') xLeftCrotchet --special character [ not escaped; works
,patindex('%[[]%','111111[11') leftCrotchetEscaped --special character [ escaped to [[]; doesn't work
,patindex('%]%' ,'111111]11') rightCrotchet --special character ] not escaped; doesn't work
,patindex('%[]]%','111111]11') xRightCrotchetEscaped --special character ] escaped to []]; also doesn't work
--where doesn't match
,patindex('%[^-]%' ,'------1--') dash --not a special character, so works without escaping
,patindex('%[^[]%' ,'[[[[[[1[[') leftCrotchet --special character [ not escaped; works
,patindex('%[^[[]]%','[[[[[[1[[') xLeftCrotchetEscaped --special character [ escaped to [[]; doesn't work
,patindex('%[^]]%' ,']]]]]]1]]') xRightCrotchet --special character ] not escaped; doesn't work
,patindex('%[^[]]]%',']]]]]]1]]') xRightCrotchetEscaped --special character ] escaped to []]; also doesn't work
In some cases it makes sense why this doesn't work; i.e. where a special character has not been correctly escaped.
However, for the left crotchet, whether it needs to be escaped or not depends on whether it follows a caret (i.e. whether we're matching on this character, or all characters but this character).
For the right crotchet, there seems to be no way to match all characters other than right crotchet; i.e. no simple way to escape this character.
NB: This post states that square brackets don't need to be escaped; but that's not the case in (one scenario from) the above example. escape square brackets in PATINDEX with SQL Server
