Remove all dots from a String except first, with Oracle REGEXP_REPLACE

Question

I want to use the Oracle REGEXP_REPLACE to remove some dots from a String. I have to remove from the second dot if exists. For example:

4       => 4
5       => 5
5.1     => 5.1
5.1.1   => 5.11
5.1.2   => 5.12
5.1.2.1 => 5.121
5.1.2.2 => 5.122
6       => 6

I have this

select REGEXP_REPLACE(num, '(\d+)(\.)(\d+)(\.)(\d+)', '\1.\3\5')
from my_table;

the problem is that my query is made just for two dots. If the string has more than two dots, I have to modify the query to accept more dots and so. Is there any way to do this automatically?

Thanks

OscarSan · Accepted Answer · 2012-06-12 18:28:03Z

2

A posible "dirty" solution

select REGEXP_REPLACE(num, '(\d+)(\.)(.*)', '\1\2') || 
       REPLACE( REGEXP_REPLACE(num, '(\d+)(.*)', '\2') ,'.','') 
from my_table;

and although it's not at all clear, it works.

edited Jun 12, 2012 at 18:28

answered Jun 12, 2012 at 18:20

OscarSan

4638 silver badges24 bronze badges

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

Comments

Jeffrey Kemp · Accepted Answer · 2012-06-13 07:29:09Z

1

SELECT SUBSTR(mystr, 1, INSTR(mystr,'.'))
       || REPLACE(SUBSTR(mystr, INSTR(mystr,'.')+1),'.')
FROM my_table;

answered Jun 13, 2012 at 7:29

Jeffrey Kemp

60.5k15 gold badges111 silver badges161 bronze badges

Collectives™ on Stack Overflow

Remove all dots from a String except first, with Oracle REGEXP_REPLACE

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related