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.
-
I believe you need to escape the parentheses.Aleks G– Aleks G2013-04-17 08:15:53 +00:00Commented Apr 17, 2013 at 8:15
-
Do you mean '/' symbol? Could you, please give me an example?Sergey– Sergey2013-04-17 08:23:50 +00:00Commented Apr 17, 2013 at 8:23
Add a comment
|
1 Answer
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>)]
11 Comments
Sergey
Thanks, Vincent! But, if I need replace only first appearance in the string?
Vincent Malgrat
This was not obvious from the statements in your question =) See my updated answer
Sergey
Yeah, I understood it, sorry)
Sergey
It seems, I asked wrong question ..I'll try to explain in detail.. Vincent, could you write me on my email? [email protected] I write my program code of my function..
Alex Poole
@Sergey - Vincent has answered your question, and your amended requirements. If you asked completely the wrong question then please ask another new question, don't try to tack it onto this one or significantly change what this one was asking.
|