CODEWITHSUNDEEP
sqlstringoracle-databasesplitconcatenation
Kuntal Maity
Kuntal Maity

Reputation: 1

substring after split by a separator oracle

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;

Upvotes: 0

Views: 143

Answers (3)

Thorsten Kettner
Thorsten Kettner

Reputation: 94884

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

Upvotes: 0

Littlefoot
Littlefoot

Reputation: 142705

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>

Upvotes: 0

GMB
GMB

Reputation: 222432

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

Upvotes: 1

Related Questions