Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, anil.

Asked: January 25, 2022 - 6:49 am UTC

Last updated: January 31, 2022 - 1:53 am UTC

Version: 19

Viewed 1000+ times

You Asked

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.

and Chris said...

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.

Rating

  (1 rating)

Comments

PACKAGE

ANIL DEVA, January 29, 2022 - 5:45 am UTC

YOU HAVE USED PACKAGE IN THE QUERY.I DIDN'T GET THAT PACKAGE.COULD YOU EXPLAIN PLEASE?

Connor McDonald
January 31, 2022 - 1:53 am UTC

It is referenced in the blog post link in the answer

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.