replacing square brackets of a string with oracle REGEXP_REPLACE function

Question

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?

score 7 · Accepted Answer · edited Oct 17 '13 at 07:24

7

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;

Here is a sqlfiddle demo

edited Oct 17 '13 at 07:24

Frank Schmitt

30,195
12
73
107

answered Oct 17 '13 at 07:04

A.B.Cade

16,735
1
37
53

As a developer have common tendency to use regular expression for replacing and have no idea about translate() function of oracle. Now I am using translate() as suggested by you. Thanks – Pokuri Oct 17 '13 at 08:04

score 3 · Answer 2 · edited May 23 '17 at 11:57

3

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.

edited May 23 '17 at 11:57

Community

1
1

answered Oct 17 '13 at 07:37

Frank Schmitt

30,195
12
73
107

The documentation link given is for MySQL but the question is for Oracle - a better link would be [Oracle 12c Database SQL Language Reference - Multilingual Regular Expression Syntax](https://docs.oracle.com/database/121/SQLRF/ap_posix001.htm#SQLRF55540). – MT0 May 12 '20 at 10:47

score 0 · Answer 3 · edited Jun 20 '20 at 09:12

To explain what is happening. Your regular expression [\[\]] is matching:

A bracket expression [\[\] 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).
Followed by a ] 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;

replacing square brackets of a string with oracle REGEXP_REPLACE function

3 Answers3

Linked

Related