swap string value in oracle #2-CodePudding

I previously addressed with an identical question(swap string value in oracle) , but there is one more moment.

I have a field cr_id that is filled in like this:

| cr_id
-----------------------------------------
| [DL_KZ#123456789][ID#123456789][PART#1234567][NEXT_STEP#0]

But I need the ID field to be filled in first, and then the DL_KZ field, next PART AND NEXT_STEP

| cr_id
-----------------------------------------
[ID#123456789][DL_KZ#123456789][PART#1234567][NEXT_STEP#0]

I tried to modify this select:

REGEXP_REPLACE(cr_id,'^(\[.*\])(\[.*\])$','\2\1')

But in the end, the order turned out to be different or even numbers appeared at the beginning of the answer

CodePudding user response：

Now it contains 4 parts, not just two, so you have to reflect that in code:

SQL> with test (cr_id) as
  2    (select '[DL_KZ#123456789][ID#123456789][PART#1234567][NEXT_STEP#0]' from dual)
  3  select regexp_replace(cr_id, '^(\[.*\])(\[.*\])(\[.*\])(\[.*\])$', '\2\1\3\4') result
  4  from test;

RESULT
----------------------------------------------------------
[ID#123456789][DL_KZ#123456789][PART#1234567][NEXT_STEP#0]

SQL>