character range including closing square bracket in PATINDEX

Question

There are similar questions, but I haven't been able to find an answer for this specific case.

I'm trying to perform a replace on a column so that anything within any types of bracket will be replaced with a hardcoded string.

e.g. the string 012345[678] would be changed to 012345[XXXXX] This should apply to any types of bracket, so 012345{678} would become 012345{XXXXX} as well.

I've been 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

It's that final PATINDEX that's giving me the problem, since the closing square bracket ends the group denoted by the [] syntax. I've tried to escape it by enclosing it in nested brackets but it doesn't seem to work. I'm drawing a blank apart from adding an extra case for square brackets and using CHARINDEX. Any better ideas?

Answer 1

Another workaround would be to use a combo of isnull and nullif instead 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

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

:)