swap string value in oracle-CodePudding

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

| cr_id
-----------------------------------------
| [DL_KZ#123456789][ID#123456789]
| [DL_KZ#987654321][ID#987654321]
| [DL_KZ#987654321][ID#123456789,987654321]

But I need the ID field to be filled in first, and then the DL_KZ field:

    | cr_id
    -----------------------------------------
    | [ID#123456789][DL_KZ#123456789]
    | [ID#987654321][DL_KZ#987654321]
    | [ID#123456789,987654321][DL_KZ#987654321]

How it possible to do?

CodePudding user response：

Simple & fast substr instr combination:

Sample data:

SQL> with test (cr_id) as
  2    (select '[DL_KZ#123456789][ID#123456789]'           from dual union all
  3     select '[DL_KZ#987654321][ID#987654321]'           from dual union all
  4     select '[DL_KZ#987654321][ID#123456789,987654321]' from dual
  5    )

Query:

  6  select substr(cr_id, instr(cr_id, '[', 1, 2)) ||
  7         substr(cr_id, 1, instr(cr_id, ']')) result
  8  from test;

RESULT
--------------------------------------------------------------------------------
[ID#123456789][DL_KZ#123456789]
[ID#987654321][DL_KZ#987654321]
[ID#123456789,987654321][DL_KZ#987654321]

SQL>

CodePudding user response：

An option is using REGEXP_REPLACE() function in which the trick is to make the replace_string argument being composed of two backreferences \2\1 with respect to this order in order to swap respective capture groups such as

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

Demo