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?

Possibly related: http://stackoverflow.com/questions/15290737/escaping-and-characters-in-a-t-sql-pattern-expression-character-class — lc., Feb 17 '14 at 16:44
This is a very good question. `PATINDEX` does not support `ESCAPE`. https://www.google.com/webhp?complete=1&hl=en#complete=1&hl=en&q=PATINDEX+escape — usr, Feb 17 '14 at 16:44

score 3 · Accepted Answer · answered Feb 17 '14 at 18:17

3

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

answered Feb 17 '14 at 18:17

Mikael Eriksson

136,425
22
210
281

2

I like this one. Not the most readable but any serious use of PATINDEX tends to be an unreadable mess anyway. – Jaloopa Feb 18 '14 at 16:32

score 1 · Answer 2 · answered Feb 17 '14 at 18:38

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

:)

character range including closing square bracket in PATINDEX

2 Answers2