CODEWITHSUNDEEP
sqlregexoracle-database
Sergey
Sergey

Reputation: 167

How can I replace brackets to hyphens within Oracle REGEXP_REPLACE function?

I'm a newbie in regular expressions. I want to replace any text string symbols like (,),[,] to hyphens (for example):

SELECT REGEXP_REPLACE ('(<FIO>)', '(', '-') FROM dual;

This gives me ORA-12725 error.

Please, explain me, what's wrong? Thanks.

Upvotes: 1

Views: 4674

Answers (1)

Vincent Malgrat
Vincent Malgrat

Reputation: 67722

To replace symbols, use the TRANSLATE function, it is less processor-intensive than regular expression functions:

SQL> SELECT translate ('(<FIO>)', '()[]', '----') replaced FROM dual;

REPLACED
--------
-<FIO>-

Regular expressions are more versatile and can do more complex things but are more expensive. In this case, replacing one character by another is done more efficiently by a specialized function. If you really want to use regular expressions, you could use REGEXP_REPLACE:

SQL> SELECT regexp_replace ('[(<FIO>)]', '[]()[]', '-', 1, 0) reg FROM dual;

REG
---------
--<FIO>--

Update: If you want to replace only the first symbol, translate won't work. Instead, use:

SQL> SELECT regexp_replace ('[(<FIO>)]', '[]()[]', '-', 1, 1) reg FROM dual;

REG
---------
-(<FIO>)]

Upvotes: 3

Related Questions