Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How to replace every Nth specific char in a string using REGEX_REPLACE

Prateek MohanFeb 28 2022

Hi Guys,
I am trying to replace every 3rd comma in a string with a newline and tried this code. But it doesn't print all the preceding tokens.
What am I doing wrong here? How do I reference all the previous tokens when I match more than 1?
--QUERY

SELECT
select --TEXT,
    REGEXP_REPLACE(TEXT,
        '(\s*[0-9]*,?){0,3}',
        CHR(10) || '\1'
    ) RES
from
(
  select 
    '115671, 115672, 115673, 115674, 115675, 115676, 115677, 115678, 115679, 115680' TEXT
  from dual
) T1;

--RETURNS
 115673,
 115676,
 115679,
--DESIRED RESULT IS
115671, 115672, 115673,
115674, 115675, 115676,
115677, 115678, 115679,
115680

Comments
Post Details
Added on Feb 28 2022
3 comments
2,132 views