• Questions
• # Regexp_replace or replace function to replace every occurance of matching pattern

## Question and Answer

Thanks for the question, Chandra.

Asked: February 25, 2021 - 9:56 pm UTC

Last updated: March 02, 2021 - 6:51 pm UTC

Version: Oracle 12c

Viewed 100+ times

Hi Tom,

Thanks in advance. I am trying to replace naked decimal with '0.'

Here is the example.

String: '.45ML-.91ML-25MCG/.5ML-.9ML(.3ML)-25.5ML or .45'

Every occurrence of naked decimal point should be replaced with '0.' resulting in

'0.45ML-0.91ML-25MCG/0.5ML-0.9ML(0.3ML)-25.5ML or 0.45'. Please note 25.5 in the string is not naked decimal and remains as is.

I tried to achieve using replace function but am not totally confident of the solution.

with str_rec as (
SELECT
'.45ML-.91ML-25MCG/.5ML-.9ML(.3ML)-25.5ML or  .45' str
from dual
)
select  case when substr(str, 1, 1) = '.' then
regexp_replace(replace(
replace(
replace(
replace(
replace( str, ' .', ' 0.'),
'/.', '/0.'),
'\.', '\0.'),
'(.', '(0.'),
'-.', '-0.'), '[.]', '0.', 1, 1)
else
replace(
replace(
replace(
replace(
replace( str, ' .', ' 0.'),
'/.', '/0.'),
'\.', '\0.'),
'(.', '(0.'),
'-.', '-0.')
end,
regexp_count(str, '[.]', 1, 'i')
from str_rec;

Can we achieve this using regexp_replace or is there a better way to do this.

Thanks

## and we said...

You could do something like this:

Match the start-of-line or any character that is not a number

The start-of-line is caret "^"

To match any character NOT in a list, use square brackets with caret as the first character to start an "anti-match". Then a list of the characters you want to avoid. Giving [^0-9]

You want either of these so you need the choice operator pipe "|" to OR them. Also place these in parentheses so you can save this character for replacement:

(^|[^0-9])

Followed by a dot

You need to escape this with a backslash because period on its own matches any character:
\.

So the complete pattern to match is:

(^|[^0-9])\.

Replace this with zero followed by the matched character and dot

You can reference the saved character with \1; the number is the position in the regex of the subexpression you want to recall.

\10.

Which gives:

with str_rec as (
select
'.45ML-.91ML-25MCG/.5ML-.9ML(.3ML)-25.5ML or  .45' str
from dual
)
select regexp_replace (
str, '(^|[^0-9])\.', '\10.'
)
from   str_rec;

REGEXP_REPLACE(STR,'(^|[^0-9])\.','\10.')
0.45ML-0.91ML-25MCG/0.5ML-0.9ML(0.3ML)-25.5ML or  0.45    

This does of course assume that you don't have periods in the string for other reasons

## Rating

(6 ratings)

A reader, February 26, 2021 - 5:16 pm UTC

Chandra, February 27, 2021 - 3:28 am UTC

This is perfect. Just out of curiosity is there a way to put a check to see if the decimal is in between character and number and not embedded between two characters.

Thanks
March 01, 2021 - 1:34 pm UTC

You can use this to check if a dot is between a letter and a number:

[[:alpha:]]\.[0-9]

You could use A-z instead of [:alpha:], but that runs into issues with accented characters

### Finding decimal between alpha and number

Chandra, March 02, 2021 - 6:39 pm UTC

Hi Chris,

I tried that approach to identify decimal embedded between alpha and number. but the result has put '0.' for every character after the match.

example:
String: abc\def.24ghi.xyz

resulted in abc\def0.20.4ghi.xyz

Thanks

March 02, 2021 - 6:51 pm UTC

What exactly was your regex and the replacement? Show us your complete regexp_replace call!

### Finding decimal between alpha and number

Rajeshwaran, Jeyabal, March 03, 2021 - 11:49 am UTC

were you looking for something like this ?
demo@XEPDB1> select str, regexp_replace( str,'(\D)\.(\d+)','\10.\2') str2
2  from (
3  select 'abc\def.24ghi.xyz' str from dual )
4  /

STR                            STR2
------------------------------ ------------------------------
abc\def.24ghi.xyz              abc\def0.24ghi.xyz


### regexp_replace naked decimal with 0.

Chandra, March 13, 2021 - 1:13 am UTC

Here is the regexp_replace for the string including decimal in the first position.
select str, regexp_replace('.45ML-.91ML-25MCG/.5ML-.9ML(.3ML)-25.5ML or  .45', '^\.|(\D)\.(\d+)', '\10.\2') str2
from (
select
'.45ML-.91ML-25MCG/.5ML-.9ML(.3ML)-25.5ML or  .45' str
from dual )
;

Thanks

### Replace naked decimal with "0."

Chandra, March 13, 2021 - 1:17 am UTC

This takes into consideration decimal in the first place as well.
set line 200
select str, regexp_replace('.45ML-.91ML-25MCG/.5ML-.9ML(.3ML)-25.5ML or  .45', '^\.|(\D)\.(\d+)', '\10.\2') str2
from (
select
'.45ML-.91ML-25MCG/.5ML-.9ML(.3ML)-25.5ML or  .45' str
from dual )
;

STR                                              STR2
------------------------------------------------ ------------------------------------------------------
.45ML-.91ML-25MCG/.5ML-.9ML(.3ML)-25.5ML or  .45 0.45ML-0.91ML-25MCG/0.5ML-0.9ML(0.3ML)-25.5ML or  0.45
1 row selected.

# More to Explore

##### SQL

The Oracle documentation contains a complete SQL reference.