A netizen on CSDN needs a solution for SQL statements. This is to replace multiple oracle characters (with test data)
A netizen on CSDN needs a solution for SQL statements. This is to replace multiple oracle characters (with test data)
The Code is as follows:
Create table A_TEST
(
PAYOUT_ITEM_CODE VARCHAR2 (30) not null,
FORMULA_DET VARCHAR2 (1000)
)
Create table B _TEST
(
ELEMENT_ID VARCHAR2 (5) not null,
NAME VARCHAR2 (41)
)
Replace ELEMENT_ID in the FORMULA_DET column with NAME
The test data is as follows:
The Code is as follows:
Insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
Values ('20140901', '({30228} + {30015}) * 100 ');
Insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
Values ('20140901', '({30102} + {30015}) * 100 ');
Insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
Values ('20140901', '({30102} + {30015}) * 100 ');
Insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
Values ('20140901', '({30102} + {30015}) * 100 ');
Insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
Values ('20140901', '({30102} + {30015}) * 100 ');
Insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
Values ('20140901', '({30102} + {30015}) * 100 ');
Insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
Values ('20140901', '({30104} + {30015}) * 30016*12 ');
Insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
Values ('20140901', '{30226} * 100000 ');
Insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
Values ('20140901', '({30102} + {30015}) * 100 ');
Insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
Values ('20140901', '({30102} + {30015}) * 100 ');
Insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
Values ('20140901', '({30102} + {30015}) * 100 ');
Insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
Values ('20140901', '{30226} + {30006} + {30061 }');
Insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
Values ('20140901', '{30226} * 30057*12 ');
Insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
Values ('20140901', '({30210} + {30030} + {30031}) * 30032 + {38000} * 30033 ');
Insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
Values ('20140901', '({30210} + {30030} + {30031} + {30032}) * 30033 + {38000} * 30036 ');
Insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
Values ('20140901', '({30229} + {30015}) * 100 ');
Insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
Values ('20140901', '({30228} + {30015}) * 100 ');
Insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
Values ('20140901', '({30216} + {30015}) * 100 ');
Insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
Values ('20140901', '({30216} + {30015}) * 100 ');
Insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
Values ('20140901', '({30307} + {30015}) * 100 ');
Insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
Values ('000000', '{30302 }');
Insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
Values ('000000', '{30302 }');
Insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
Values ('000000', '{30302 }');
Insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
Values ('000000', '{30302 }');
Insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
Values ('000000', '{30302 }');
Insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
Values ('000000', '{30302 }');
Insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
Values ('20140901', '{30226} * 100000 ');
Insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
Values ('20140901', '{30226} * 100000 ');
Insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
Values ('20140901', '{30226} * 30057*12 ');
Insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
Values ('20140901', '{30226} * 30057*12 ');
Insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
Values ('20140901', '{30226} * 0 ');
Insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
Values ('20140901', '{30226}/{30057} * 30057 ');
Insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
Values ('20140901', '{30226} * 100000 ');
The Code is as follows:
Insert into B _test (ELEMENT_ID, NAME)
Values ('201312', 'a1 ');
Insert into B _test (ELEMENT_ID, NAME)
Values ('20140901', 'a2 ');
Insert into B _test (ELEMENT_ID, NAME)
Values ('20140901', 'a3 ');
Insert into B _test (ELEMENT_ID, NAME)
Values ('20140901', 'a4 ');
Insert into B _test (ELEMENT_ID, NAME)
Values ('20140901', 'a5 ');
Insert into B _test (ELEMENT_ID, NAME)
Values ('20140901', 'a6 ');
Insert into B _test (ELEMENT_ID, NAME)
Values ('20140901', 'a7 ');
Insert into B _test (ELEMENT_ID, NAME)
Values ('20140901', 'a8 ');
Insert into B _test (ELEMENT_ID, NAME)
Values ('20140901', 'a9 ');
Insert into B _test (ELEMENT_ID, NAME)
Values ('20140901', 'a10 ');
Insert into B _test (ELEMENT_ID, NAME)
Values ('20140901', 'a11 ');
Insert into B _test (ELEMENT_ID, NAME)
Values ('123', 'a12 ');
Insert into B _test (ELEMENT_ID, NAME)
Values ('20140901', 'a13 ');
Insert into B _test (ELEMENT_ID, NAME)
Values ('20140901', 'a14 ');
Insert into B _test (ELEMENT_ID, NAME)
Values ('20140901', 'a15 ');
Insert into B _test (ELEMENT_ID, NAME)
Values ('20140901', 'a16 ');
Insert into B _test (ELEMENT_ID, NAME)
Values ('20140901', 'a17 ');
Insert into B _test (ELEMENT_ID, NAME)
Values ('20140901', 'a18 ');
Insert into B _test (ELEMENT_ID, NAME)
Values ('20140901', 'a19 ');
Insert into B _test (ELEMENT_ID, NAME)
Values ('20180101', 'a20 ');
Insert into B _test (ELEMENT_ID, NAME)
Values ('000000', 'a21 ');
Insert into B _test (ELEMENT_ID, NAME)
Values ('20140901', 'a22 ');
Insert into B _test (ELEMENT_ID, NAME)
Values ('20140901', 'a23 ');
Insert into B _test (ELEMENT_ID, NAME)
Values ('20140901', 'a24 ');
Insert into B _test (ELEMENT_ID, NAME)
Values ('20140901', 'a25 ');
Insert into B _test (ELEMENT_ID, NAME)
Values ('20140901', 'a26 ');
Insert into B _test (ELEMENT_ID, NAME)
Values ('20140901', 'a27 ');
Insert into B _test (ELEMENT_ID, NAME)
Values ('20140901', 'a28 ');
Insert into B _test (ELEMENT_ID, NAME)
Values ('20140901', 'a29 ');
Insert into B _test (ELEMENT_ID, NAME)
Values ('20140901', 'a30 ');
Insert into B _test (ELEMENT_ID, NAME)
Values ('20140901', 'a31 ');
Insert into B _test (ELEMENT_ID, NAME)
Values ('20140901', 'a32 ');
Insert into B _test (ELEMENT_ID, NAME)
Values ('20140901', 'a33 ');
Insert into B _test (ELEMENT_ID, NAME)
Values ('000000', 'a34 ');
Insert into B _test (ELEMENT_ID, NAME)
Values ('20140901', 'a35 ');
Insert into B _test (ELEMENT_ID, NAME)
Values ('20140901', 'a36 ');
Insert into B _test (ELEMENT_ID, NAME)
Values ('20140901', 'a37 ');
Insert into B _test (ELEMENT_ID, NAME)
Values ('20140901', 'a38 ');
Insert into B _test (ELEMENT_ID, NAME)
Values ('20140901', 'a39 ');
Insert into B _test (ELEMENT_ID, NAME)
Values ('20140901', 'a40 ');
Insert into B _test (ELEMENT_ID, NAME)
Values ('20140901', 'a41 ');
Insert into B _test (ELEMENT_ID, NAME)
Values ('20140901', 'a42 ');
Insert into B _test (ELEMENT_ID, NAME)
Values ('20140901', 'a43 ');
Insert into B _test (ELEMENT_ID, NAME)
Values ('000000', 'a44 ');
Insert into B _test (ELEMENT_ID, NAME)
Values ('000000', 'a45 ');
Insert into B _test (ELEMENT_ID, NAME)
Values ('000000', 'a46 ');
Insert into B _test (ELEMENT_ID, NAME)
Values ('20140901', 'a47 ');
Insert into B _test (ELEMENT_ID, NAME)
Values ('20140901', 'a48 ');
Insert into B _test (ELEMENT_ID, NAME)
Values ('000000', 'a49 ');
Insert into B _test (ELEMENT_ID, NAME)
Values ('20140901', 'a50 ');
Insert into B _test (ELEMENT_ID, NAME)
Values ('000000', 'a51 ');
If the function or sp is used, there is no difficulty.
However, it is more difficult to use SQL.
The Code is as follows:
Select gid, payout_item_code, formula_det, max (substr (txt, 1, length (txt)-1) from (
Select a. gid,
A. payout_item_code,
A. formula_det,
Replace (sys_connect_by_path (decode (B. element_id, null,. signal, replace (signal, B. element_id, B. name), '#'), '#', '') txt
From
(Select gid, payout_item_code, formula_det, row_number () over (partition by gid order by level) rn,
Substr (formula_det, decode (rownum-(allcnt-selfcnt), 1, 1, instr (formula_det, '}', 1, rownum-(allcnt-selfcnt)-1) + 1), instr (formula_det, '}', 1, rownum-(allcnt-selfcnt)-decode (rownum-(allcnt-selfcnt), 1, 0, instr (formula_det, '}', 1, rownum-(allcnt-selfcnt)-1) signal
From (select a. payout_item_code, a. rowid gid,
A. formula_det | '}' formula_det,
Length (a. formula_det )-
Length (replace (a. formula_det, '}', '') + 1 selfcnt,
Sum (length (. formula_det)-length (replace (. formula_det, '}', '') + 1) over (order by rowid) allcnt, sum (length (. formula_det)-length (replace (. formula_det, '}', '') + 1) over () sumcnt
From a_test a) t1
Start with (allcnt-selfcnt) = 0 connect by rownum <sumcnt + 1 and instr (formula_det, '}', 1, rownum-(allcnt-selfcnt)> 0)
Left join B _test B on instr (a. signal | '}', '{' | B. element_id | '}', 1, 1)> 0
Start with a. rn = 1 connect by prior a. gid = a. gid and prior a. rn + 1 = a. rn)
Group by gid, payout_item_code, formula_det
