CODEWITHSUNDEEP
sqloracle-databaseoracle11gregexp-replace
Miracle
Miracle

Reputation: 387

oracle sql regexp_replace

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.

Upvotes: 3

Views: 3926

Answers (3)

Jay Shankar Gupta
Jay Shankar Gupta

Reputation: 6088

SELECT REGEXP_REPLACE(expCol, '\.([a-zA-Z0-9])', '. \1') AS expCol FROM expTable

OR

SELECT REGEXP_REPLACE(expCol, '[.]([a-zA-Z0-9])', '. \1') AS expCol FROM expTable

Output

EXPCOL
abc. abc
bcd. 123
efg.@/.

LiveDemo

http://sqlfiddle.com/#!4/0a6e0/31

Upvotes: 3

D-Shih
D-Shih

Reputation: 46219

You can try this.

. is a keyword in regex so you need put \ in front of it 

SELECT REGEXP_REPLACE(expCol, '\.(\w)', '. \1') from T

sqlfiddle :http://sqlfiddle.com/#!4/94ffec/1

Upvotes: 2

Kaushik Nayak
Kaushik Nayak

Reputation: 31648

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

Upvotes: 3

Related Questions