Character range, including trailing square bracket in PATINDEX

Question

There are similar questions, but I could not find the answer for this particular case.

I am trying to do a replacement in a column, so that anything inside any type of parentheses will be replaced with a hard-coded string.

eg. the string 012345[678]will be changed to 012345[XXXXX] This should apply to any type of parentheses, therefore 012345{678}it will also become 012345{XXXXX}.

I am trying with PATINDEX:

SELECT CASE WHEN PATINDEX('%[([<{]%', column1) = 0
           THEN column1
       ELSE LEFT(column1, PATINDEX('%[([<{]%', column1))
             + 'XXXXX'
             + SUBSTRING(column1, PATINDEX('%[)>}[]]]%', column1), 1)
       END
FROM mytable

This is the last PATINDEX that gives me the problem, since the closing square bracket ends with the group indicated by the syntax []. I tried to escape from it by including it in nested brackets, but it does not seem to work. I am drawing a space other than adding an extra case for square brackets and using CHARINDEX. Any better ideas?

Answer 1

Another workaround would be to use combos isnulland nullifinstead of adding an extra case for square brackets.

SELECT CASE WHEN PATINDEX('%[([<{]%', column1) = 0
           THEN column1
       ELSE LEFT(column1, PATINDEX('%[([<{]%', column1))
             + 'XXXXX'
             + SUBSTRING(column1, ISNULL(NULLIF(PATINDEX('%[)>}]%', column1), 0), CHARINDEX(']', column1)), 1)
       END
FROM myTable

Answer 2

to try:

SELECT CASE WHEN PATINDEX('%[([<{]%', column1) = 0
           THEN column1
       ELSE LEFT(column1, PATINDEX('%[([<{]%', column1))
             + 'XXXXX'
             + CASE WHEN LEN(SUBSTRING(column1, PATINDEX('%[)>}]%',column1), 1)) > 0
             THEN SUBSTRING(column1, PATINDEX('%[)>}]%',column1), 1)
             ELSE SUBSTRING(column1, CHARINDEX(']',column1), 1) END
       END
FROM mytable

:)