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.

Kaushik Nayak · Accepted Answer · 2018-04-11 06:47:36Z

3

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

edited Apr 11, 2018 at 6:47

answered Apr 11, 2018 at 6:41

Kaushik Nayak

32k6 gold badges36 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jay Shankar Gupta · Accepted Answer · 2018-04-11 09:17:23Z

3

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

edited Apr 11, 2018 at 9:17

answered Apr 11, 2018 at 6:28

Jay Shankar Gupta

6,1281 gold badge14 silver badges31 bronze badges

5 Comments

MT0 Over a year ago

This changes abc/123 to abc. 123 which is wrong. SQLFIDDLE [/.] will match the / character or the . character - you want [.] or \. instead. Also, you do not want to use REGEXP_LIKE and then a different expression in REGEXP_REPLACE as 'abc.123.@/.' will match the REGEXP_LIKE and then REGEXP_SUBSTR will change it to 'abc. 123. @/. ' which is, again, wrong.

Jay Shankar Gupta Over a year ago

sorry typing mistake

MT0 Over a year ago

Still wrong as [\.] will match both \ and . characters SQLFIDDLE. Also, the edit did nothing to address the second error.

Jay Shankar Gupta Over a year ago

it's correct now I think @MT0 Thanks for different test case

MT0 Over a year ago

Still wrong as [\.] will match both \ and . characters. SQLFIDDLE - look at the difference in the last row of the output. You want either '[.]([a-zA-Z0-9])' or '\.([a-zA-Z0-9])' as the regular expression.

D-Shih · Accepted Answer · 2018-04-11 07:03:29Z

2

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

edited Apr 11, 2018 at 7:03

answered Apr 11, 2018 at 6:32

D-Shih

46.5k6 gold badges37 silver badges56 bronze badges

2 Comments

Nishant Gupta Over a year ago

REGEXP_REPLACE(EXPCOL,'\.\W','.') abc. bc bcd. 23 efg.@/. Your output is wrong it replace 1st character after .

D-Shih Over a year ago

Guys you are right :(.I edit my answer . I so sorry about it.

Collectives™ on Stack Overflow

oracle sql regexp_replace

3 Answers 3

Comments

5 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

5 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related