I am trying to replace two dots following each other with Null.
SQL> select regexp_replace('..','[^\.]+\.','Null') from dual;
produces:
RE
--
..
whereas I want:
RE
--
Null
How do I achieve this using Regex?
Add a comment
|
3 Answers
Try the following:
SELECT regexp_replace( '..', '\.{2}', 'Null' ) FROM dual;
Your example is using the ^ symbol for negation ("do not match a ."), which I'm not sure is your intention. If you are instead wanting to match .. only at the beginning of a string, use ^ as follows:
SELECT regexp_replace( '..', '^\.{2}', 'Null' ) FROM dual;
3 Comments
jhon.smith
Brilliant not sure why mine didnt work and strange neither this works select regexp_replace('..','[^\.]{2}','Null') from dual;
SetFreeByTruth
@jhon.smith: ^ has two distinct uses in Regex: 1) for negation inside a character set (which is how you're using it) and 2) to match the beginning of a string. [^\.]{2} instructs Regex to "match two consecutive NON-DOT characters", whereas ^[\.]{2} says "match two dots at the beginning of a string".
jhon.smith
Thanks brilliant explanation as well why mine did not work :-)
This is going to look a little hairy but for the regex parameter:
(?!\.{3,})\.{2}(?<!\.{3,})
That will make sure there's not any more or any less than 2 .. following each other, from either the front or the back.
So it'll only match .., not ..., not ., etc. I'm assuming this is what you want.
1 Comment
jhon.smith
Heck Jason yes you hacked my mind no computer on the universe can read my mind and give me an answer
If you're looking for two dots at the beginning of a line, perhaps the simplest expression which could possibly work is
SELECT REGEXP_REPLACE('..', '^\.\.', 'Null') FROM DUAL
If you're looking for two dots anywhere in the line remove the ^ at the beginning of the expression.
If you're looking for two dots which comprise the entire line, add $ at the end of the expression, as in
'^\.\.$'
Documentation for regular expression elements supported by Oracle can be found here.
Share and enjoy.
answered Jul 20, 2012 at 11:09
Bob Jarvis - Слава Україні
50.2k10 gold badges81 silver badges119 bronze badges