Like if I have a string "123456,852369,7852159,1596357" The out put looking for "1234,8523,7852,1596"

Requirement is....we want to collect 4 char after every ',' separator

like split, substring and again concat

select
  REGEXP_REPLACE('MEDA,MEDA,MEDA,MEDA,MEDA,MEDA,MEDA,MEDA,MDCB,MDCB,MDCB,MDCB,MDCB,MDCB', '([^,] )(,\1) ', '\1')
from dual;

CodePudding user response：

we want to collect 4 char after every ',' separator

Here is an approach using regexp_replace:

select regexp_replace(
    '123456,852369,7852159,1596357',
    '([^,]{4})[^,]*(,|$)',
    '\1\2'
)
from dual

Regexp breakdown:

([^,]{4})    4 characters others than "," (capture that group as \1)
[^,]*        0 to n characters other than "," (no capture)
(,|$)        either character "," or the end of string (capture this as \2)

The function replaces each match with capture 1 (the 4 characters we want) followed by capture 2 (the separator, if there is one).

Demo:

RESULT
1234,8523,7852,1596

CodePudding user response：

One option might be to split the string, extract 4 characters and aggregate them back:

SQL> with test (col) as
  2    (select '123456,852369,7852159,1596357' from dual)
  3  select listagg(regexp_substr(col, '[^,]{4}', 1, level), ',')
  4         within group (order by level) result
  5  from test
  6  connect by level <= regexp_count(col, ',')   1;

RESULT
--------------------------------------------------------------------------------
1234,8523,7852,1596

SQL>

CodePudding user response：

With REGEX_REPLACE:

select regexp_replace(the_string, '(^|,)([^,]{4})[^,]*', '\1\2')
from mytable;

This looks for

1. the beginning of the string or the comma
2. then four characters that are not a comma
3. then any number of trailing characters that are not a comma

And only keeps

1. the beginning or the comma
2. the four characters that follow

Demo: https://dbfiddle.uk/efUFvKyO