CODEWITHSUNDEEP
sqloracle-database
user2176150
user2176150

Reputation: 303

Insert between numbers using Oracle

I have a string like this one:

2068414199308820683392068279206867820687022068646

I want to insert comma symbol after each seventh digit.

How can I do this?

The desired output looks like that:

Output
======
2068414,1993088,2068339,2068279,2068678,2068702,2068646

Upvotes: 1

Views: 116

Answers (1)

Nick Krasnov
Nick Krasnov

Reputation: 27251

As one of the approaches(Oracle 10g and up) you could use regexp_replace() regular expression function to get the desired result: 

SQL> with t1(col) as(
  2    select '2068414199308820683392068279206867820687022068646' from dual
  3  )
  4  select rtrim(regexp_replace(col, '([[:digit:]]{7})', '\1,'), ',') as res
  5    from t1
  6  ;

Result:

res
-------------------------------------------------------
2068414,1993088,2068339,2068279,2068678,2068702,2068646

how can i update this value to a table customer 

update your_table_name
   set col_name = rtrim( regexp_replace( col_name 
                                        , '([[:digit:]]{7})'
                                        , '\1,')
                        , ',' )
 -- where clause if needed

Upvotes: 5

Related Questions