Skip to Main Content
  • Questions
  • Regexp_replace or replace function to replace every occurance of matching pattern

Breadcrumb

Question and Answer

Connor McDonald

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

You Asked

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)

Comments

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
Chris Saxon
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


Chris Saxon
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.