character range including closing square bracket in PATINDEX

sql, sql-server-2008-r2, t-sql

Solution

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

Problem

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?

Original source