oracle sql regexp_replace

Question

I have a table that has the values like this.

ExpTable

+--------+   
|expCol  | 
+--------+
|abc.abc |
|bcd.123 |
|efg.@/. |
+--------+

And what I wanted is that when the character after the period is a letter or number, the output will add a space after the dot like this:

Expected Output

+--------+   
|expCol  | 
+--------+
|abc. abc|
|bcd. 123|
|efg.@/. | --the value here stays the same because after the period is not a letter/number
+--------+

I tried:

SELECT REGEXP_REPLACE(expCol, '.', '. ') from expTable WHERE /*condition*/

And as expected, everything including the last value 'efg.@/.' has got a space after the period. I dont know what to put in the WHERE clause.

Answer 1

You could try this. It searches for a . followed by a word character, and replaces it with a dot ., then a space and the matched character.

select REGEXP_REPLACE(expCol, '\.(\w)','. \1') FROM ExpTable;

if you only want the first such occurrence to be replaced, you could specify it.

REGEXP_REPLACE(expCol, '\.(\w)','. \1',1,1) 

Only thing to note is this would match a number,alphabet and underscore as well, if you don't want to consider "_" , use [[:alnum:]] or [a-zA-Z0-9] in place of \w

Demo