Square bracket in regexp_replace pattern-CodePudding

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

enter image description here

I tried the escape character \ but it did not work

CodePudding user response：

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

CodePudding user response：

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 -.

CodePudding user response：

In SQL, you can use the escape character \ to match a square bracket character in a regular expression. To add square brackets to the string, you can simply include them in the replacement string.

Here is an example using REGEXP_REPLACE in SQL:

SELECT REGEXP_REPLACE(
  'MSD_40001_ME_SPE__XXXX__Technical__Specification_REV9_(2021_05_27)_xls',
  '__(.*?)__',
  '_[\1]'
) AS result
FROM dual;

The result will be: MSD_40001_ME_SPE_[XXXX]_Technical__Specification_REV9_(2021_05_27)_xls