CODEWITHSUNDEEP
sqlregexoracle-databaseregexp-replace
Ivan Ivanov
Ivan Ivanov

Reputation: 525

Replacing dots between letters [ORACLE]

I have a string which contains dots. 

select 'String.Cont.ains 123.FD DF.123 11.11' str from dual

I need to replace dots to dots with space, but only when a letter is before the dot and a letter is after the dot. In all other cases the dot must not be replaced.

Can I do this with regexp_replace?

Result must be like this :

String. Cont. ains 123.FD DF.123 11.11

I'm trying to use this:

select regexp_replace('String.Cont.ains 123.FD DF.123 11.11','(\.){1,}','. ')from dual

Upvotes: 1

Views: 454

Answers (1)

Nick
Nick

Reputation: 147196

You can use this regex:

([a-z])(\.+)([a-z])

which looks for a character, one or more dots and then another character, and replace it with 

\1\2 \3

i.e. the first character, the dots, a space, and the second character. In Oracle (note the use of the i flag so we don't have to specify [A-Za-z] instead of just [a-z]):

SELECT 
REGEXP_REPLACE('String.Cont.ains 123.FD DF.123 11.11', '([a-z])(\.+)([a-z])', '\1\2 \3', 1, 0, 'i') AS new
FROM dual

Output:

NEW
String. Cont. ains 123.FD DF.123 11.11

Demo on dbfiddle

Upvotes: 3

Related Questions