Square bracket in regexp_replace pattern

Question

I want to use REGEXP_REPLACE with this pattern. but I don't know how to add square bracket in square bracket. I try to put escape character but i did not work. in this screenshot i want to also keep the [XXX] these square bracket. I need to add this square bracket somehow in my pattern. thanks.

Right now the output is this:

MSD_40001_ME_SPE__XXXX__Technical__Specification_REV9_(2021_05_27)_xls

but I want to like that:

MSD_40001_ME_SPE_[XXXX]_Technical__Specification_REV9_(2021_05_27)_xls

I tried the escape character \ but it did not work

Answer 1

You could try this regex pattern: [^][a-z_A-Z0-9()]

    SELECT REGEXP_REPLACE('MSD_40001_ME_SPE_[XXXX]_Technical_%Specification_REV@9_(2021_05_27)_xls', '[^][a-z_A-Z0-9()]', '_')
    FROM DUAL

To specify a right bracket (]) in the bracket expression, place it first in the list (after the initial circumflex (^), if any).

See demo here

Answer 2

From Regexp.Info,

One key syntactic difference is that the backslash is NOT a metacharacter in a POSIX bracket expression. So in POSIX, the regular expression [\d] matches a \ or a d. To match a ], put it as the first character after the opening [ or the negating ^. To match a -, put it right before the closing ]. To match a ^, put it before the final literal - or the closing ]. Put together, []\d^-] matches ], , d, ^ or -.

Answer 3

How about this different take on the problem? Match one or more of the following: space OR a dash OR a percent sign OR a dollar sign OR an at sign OR a period (escaping the characters that have special regex meaning) and replace with an underscore. Note this takes care of the double underscore after "Technical".

with tbl(data) as (
  select 'MSD 40001-ME-SPE-[XXXX] Technical%$Specification@REV9 (2021.05.27).xls' from dual
)
select regexp_replace(data, '( |\-|%|\$|@|\.)+', '_') fixed
from tbl;

FIXED                                                                
---------------------------------------------------------------------
MSD_40001_ME_SPE_[XXXX]_Technical_Specification_REV9_(2021_05_27)_xls