Regexp_replace processing result

Question

I have a string with groups of nubmers. And Id like to make constant length string. Now I use two regexp_replace. First to add 10 numbers to string and next to cut string and take last 10 values:

with s(txt) as ( select '1030123:12031:1341' from dual)
select regexp_replace(
       regexp_replace(txt, '(\d+)','0000000000\1')
,'\d+(\d{10})','\1') from s ;

But Id like to use only one regex something like

regexp_replace(txt, '(\d+)',lpad('\1',10,'0'))

But it don't work. lpad executed before regexp. Could you have any ideas?

No. Now it may be any count of groups from 1 to 5. But I want to make solution for any count of groups — Zhenora
– Zhenora, Commented Oct 20, 2016 at 8:59

Aleksej · Accepted Answer · 2016-10-20 09:04:56Z

1

With a slightly different approach, you can try the following:

with s(id, txt) as
(
    select rownum, txt
    from (
            select '1030123:12031:1341' as txt from dual union all
            select '1234:0123456789:1341' from dual
         )
)                 
SELECT listagg(lpad(regexp_substr(s.txt, '[^:]+', 1, lines.column_value), 10, '0'), ':') within group (order by column_value) txt
 FROM s,
   TABLE (CAST (MULTISET
   (SELECT LEVEL FROM dual CONNECT BY instr(s.txt, ':', 1, LEVEL - 1) > 0
   ) AS sys.odciNumberList )) lines
group by id

TXT
-----------------------------------
0001030123:0000012031:0000001341
0000001234:0123456789:0000001341

This uses the CONNECT BY to split every string based on the separator ':', then uses LPAD to pad to 10 and then aggregates the strings to build rows containing the concatenation of padded values

edited Oct 20, 2016 at 9:04

answered Oct 20, 2016 at 8:35

Aleksej

23.1k6 gold badges38 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Zhenora Over a year ago

Thank you. I think about it. But in a big table It will be too expensive

Zhenora Over a year ago

And in your result groups are mixed

David דודו Markovitz · Accepted Answer · 2016-10-20 09:33:22Z

0

This works for non-empty sequences (e.g. 123::456)

with s(txt) as ( select '1030123:12031:1341' from dual)

select    regexp_replace (regexp_replace (txt,'(\d+)',lpad('0',10,'0') || '\1'),'0*(\d{10})','\1')

from      s
;

answered Oct 20, 2016 at 9:33

David דודו Markovitz

45.2k7 gold badges75 silver badges97 bronze badges

5 Comments

Zhenora Over a year ago

But its the same as my current solution?

David דודו Markovitz Over a year ago

You have an empty space here: '\d+ (\d{10})

David דודו Markovitz Over a year ago

@Zhenora , Hi, since this was a right answer, do you mind marking it as a correct answer?

Zhenora Over a year ago

Yes its right answer, but it the same as mine. two regexp_replace with taking ends

David דודו Markovitz Over a year ago

@Zhenora, yours didn't work and this is why you've posted this question. You fixed it after I pointed to you where the error is, even that it was a very small error. I'm leaving you the chice to decide if to aknowledge that or not. In any case I'm glad I could help.

Collectives™ on Stack Overflow

Regexp_replace processing result

2 Answers 2

2 Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related