CODEWITHSUNDEEP
sqlregexstringoracle-database
user2647888
user2647888

Reputation: 721

How to add a delimiter at a particular position in Oracle

Hi I have a string like this 

ABCDEFGH I want the output to be ABCDEF.GH

If it's a number like 1234567 then i want the output to be 12345.67

Basically i want the delimeter (.) before last 2 characters.

Upvotes: 0

Views: 1123

Answers (1)

Frank Schmitt
Frank Schmitt

Reputation: 30815

You can use regular expressions for this:

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

This matches

  • one or more digits (\d+) (capturing them in group #1)
  • followed by exactly two digits (\d{2}) (capturing them in group #2)

and replaces this with the contents of group #1 followed by a . followed by the contents of group #2: \1.\2

Upvotes: 1

Related Questions