sql - How to add a delimiter at a particular position in Oracle -

sql - How to add a delimiter at a particular position in Oracle -

hi have string

abcdefgh want output abcdef.gh

if it's number 1234567 want output 12345.67

basically want delimeter (.) before lastly 2 characters.

you can utilize regular expressions this:

with v_data(val) ( select '123456' dual union select 'abcdef' dual union select '678' dual ) select val, regexp_replace(val, '(\d+)(\d{2})', '\1.\2') v_data

this matches

one or more digits (\d+) (capturing them in grouping #1) followed 2 digits (\d{2}) (capturing them in grouping #2)

and replaces contents of grouping #1 followed . followed contents of grouping #2: \1.\2

sql regex string oracle