In the below one string i have to split based on "@" and then have to split "_" fields and then append "^" fields to three fields of "_" values.
"08550010201_750000.00_Royalty@0_22500.00_TCS@0_75000.00_DMF@08550010210_104.00_Four Mineral Premium@
08550010210_105.00_Five Mineral Premium@08550010210_106.00_Six Mineral Premium@08550010210_107.00_Seven Mineral Premium@08550010210_108.00_Eight Mineral Premium@08550010210_109.00_Nine MineralPremium^QP1104012013^55740^P1U55740T1951918^55740195191818112021^NA^NA^LARSEN & TOUBRO LTD TIIC WDFC CTP 3R PROJECT^Royalt".
I have successfully split the string using union all but my procedure performance reduced ..procedure taking lot of time get output.
please help me to split the string.
output should be like:
08550010201 750000.00 Royalty QP1104012013 55740 P1U55740T1951918 55740195191818112021 NA NA LARSEN & TOUBRO LTD TIIC WDFC CTP 3R
0 22500.00 TCS Royalty QP1104012013 55740 P1U55740T1951918 55740195191818112021 NA NA LARSEN & TOUBRO LTD TIIC WDFC CTP 3R
0 75000.00 DMF Royalty QP1104012013 55740 P1U55740T1951918 55740195191818112021 NA NA LARSEN & TOUBRO LTD TIIC WDFC CTP 3R
08550010210 104.00 Four Mineral Premium QP1104012013 55740 P1U55740T1951918 55740195191818112021 NA NA LARSEN & TOUBRO LTD TIIC WDFC CTP 3R
08550010210 104.00 Five Mineral Premium QP1104012013 55740 P1U55740T1951918 55740195191818112021 NA NA LARSEN & TOUBRO LTD TIIC WDFC CTP 3R
08550010210 104.00 Six Mineral Premium QP1104012013 55740 P1U55740T1951918 55740195191818112021 NA NA LARSEN & TOUBRO LTD TIIC WDFC CTP 3R
08550010210 104.00 Seven Mineral Premium QP1104012013 55740 P1U55740T1951918 55740195191818112021 NA NA LARSEN & TOUBRO LTD TIIC WDFC CTP 3R
08550010210 104.00 Eight Mineral Premium QP1104012013 55740 P1U55740T1951918 55740195191818112021 NA NA LARSEN & TOUBRO LTD TIIC WDFC CTP 3R.
So you want to split up the string into rows, then append the text after the ^ to the end of each row?
Here's one way to do this:
- Use the connect by level CSV to rows trick
- Cross join these rows with the string extracted from the end value
I discuss the CSV-to-rows trick and, as you're on 19c, how to make this a SQL macro at
https://blogs.oracle.com/sql/post/split-comma-separated-values-into-rows-in-oracle-database Using this gives:
set define off
with val as (
select '08550010201_750000.00_Royalty@0_22500.00_TCS@0_75000.00_DMF@08550010210_104.00_Four Mineral Premium@08550010210_105.00_Five Mineral Premium@08550010210_106.00_Six Mineral Premium@08550010210_107.00_Seven Mineral Premium@08550010210_108.00_Eight Mineral Premium@08550010210_109.00_Nine MineralPremium^QP1104012013^55740^P1U55740T1951918^55740195191818112021^NA^NA^LARSEN & TOUBRO LTD TIIC WDFC CTP 3R PROJECT^Royalt' v
from dual
)
select regexp_replace ( str, '\^(.*)' ) || ' ' || sub str
from string_macros_pkg.split_string (
val, columns ( v ), '@'
)
cross join (
select trim ( replace ( regexp_substr ( v, '\^(.*)' ), '^', ' ' ) ) sub
from val
);
STR
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
08550010201_750000.00_Royalty QP1104012013 55740 P1U55740T1951918 55740195191818112021 NA NA LARSEN & TOUBRO LTD TIIC WDFC CTP 3R PROJECT Royalt
0_22500.00_TCS QP1104012013 55740 P1U55740T1951918 55740195191818112021 NA NA LARSEN & TOUBRO LTD TIIC WDFC CTP 3R PROJECT Royalt
0_75000.00_DMF QP1104012013 55740 P1U55740T1951918 55740195191818112021 NA NA LARSEN & TOUBRO LTD TIIC WDFC CTP 3R PROJECT Royalt
08550010210_104.00_Four Mineral Premium QP1104012013 55740 P1U55740T1951918 55740195191818112021 NA NA LARSEN & TOUBRO LTD TIIC WDFC CTP 3R PROJECT Royalt
08550010210_105.00_Five Mineral Premium QP1104012013 55740 P1U55740T1951918 55740195191818112021 NA NA LARSEN & TOUBRO LTD TIIC WDFC CTP 3R PROJECT Royalt
08550010210_106.00_Six Mineral Premium QP1104012013 55740 P1U55740T1951918 55740195191818112021 NA NA LARSEN & TOUBRO LTD TIIC WDFC CTP 3R PROJECT Royalt
08550010210_107.00_Seven Mineral Premium QP1104012013 55740 P1U55740T1951918 55740195191818112021 NA NA LARSEN & TOUBRO LTD TIIC WDFC CTP 3R PROJECT Royalt
08550010210_108.00_Eight Mineral Premium QP1104012013 55740 P1U55740T1951918 55740195191818112021 NA NA LARSEN & TOUBRO LTD TIIC WDFC CTP 3R PROJECT Royalt
08550010210_109.00_Nine MineralPremium QP1104012013 55740 P1U55740T1951918 55740195191818112021 NA NA LARSEN & TOUBRO LTD TIIC WDFC CTP 3R PROJECT Royalt
I don't fully understand why "Nine MineralPremium" is excluded from your expected output. Or why "PROJECT Royalt" is missing from the end too, but this should be enough to get you started.