How to match all characters except right crotchet

2019-08-13 19:26发布

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

0条回答
登录 后发表回答