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
You could do something like this:
Match the start-of-line or any character that is not a numberThe 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 dotYou 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