I want to replace square brackets in string with REGEXP_REPLACE function. Even I escape these chracters it's not replacing
select regexp_replace('VMI[[DATA]]INFO', '[\[\]]', '_') from dual;
result
VMI[[DATA]]INFO
How can we do this? Am I missing something?
You can do it like this:
select regexp_replace('VMI[[DATA]]INFO', '\[|\]', '_') from dual;
But I don't think that regular expressions are needed here, you can also use TRANSLATE
select translate('VMI[[DATA]]INFO', '[]', '__') from dual;
Inside character classes, you don't need escapes. Special rules apply for -, ] and ^, for obvious reasons (see e.g. List of metacharacters for MySQL square brackets )
So in your case, you can use
select regexp_replace('VMI[[DATA]]INFO', '[][]', '_') from dual;
but I agree with @A.B.Cade - regular expresssions are overkill for this.
To explain what is happening. Your regular expression [\[\]] is matching:
[\[\] which matches either a \ character or a [ character or a \ character (since \ is not an escape character in Oracle's regular expression syntax but is treated as a character literal).] character.So your regular expression would match a sub-string that was either \] or [].
From the Oracle 12c Documentation:
Bracket expression for specifying a matching list that should match any one of the expressions represented in the list. A non-matching list expression begins with a circumflex (
^) and specifies a list that matches any character except for the expressions represented in the list.To specify a right bracket (
]) in the bracket expression, place it first in the list (after the initial circumflex (^), if any).To specify a hyphen in the bracket expression, place it first in the list (after the initial circumflex (
^), if any), last in the list, or as an ending range point in a range expression.
So, if you want to match a closing square bracket in a bracket expression then it needs to be the first character in the list and your regular expression should be [][]. The first [ starts the bracket expression; the ] second character is to match a closing square bracket character; the [ third character matches an opening square bracket character; and the final ] terminates the bracket expression.
Which would give the solution as:
SELECT REGEXP_REPLACE(
'VMI[[DATA]]INFO',
'[][]',
'_'
)
FROM DUAL;
