Skip to Main Content
  • Questions

  • extracting particular pattern from data using sql

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Vino.

Asked: October 21, 2016 - 1:13 pm UTC

Last updated: October 24, 2016 - 12:18 pm UTC

Version: 11g

Viewed 1000+ times

You Asked

Dear Tom,

Need to extract particular pattern as example.

select ('|100|BXX656|:20:200100O0012|:32A:010607USD6025,10|:50:XYZ LABORATORIES PVT LTD|B/2 TESTCHAMBERS 22 B DESAI RD|CBD-26| -||||||||||||||:59:W.S.A. TEXT SZCZESIUL|USA|||:71A:BEN||||||') from dual;


output as:
pattern between |: to |:
:20:200100O0012
:32A:010607USD6025,10
thank you.

and Chris said...

There are various ways you can split a string at a given pattern. Here's one Stew Ashton came up with using ora:tokenize :

with d as (
select ('|100|BXX656|:20:200100O0012|:32A:010607USD6025,10|:50:XYZ LABORATORIES PVT LTD|B/2 TESTCHAMBERS 22 B DESAI RD|CBD-26| -||||||||||||||:59:W.S.A. TEXT SZCZESIUL|USA|||:71A:BEN||||||') d from dual
)
  select str from d, xmltable(
  'if (contains($X,"|:")) then ora:tokenize($X,"\|:") else $X'
  passing d as X
  columns str varchar2(4000) path '.'
  );

STR                                                                                
|100|BXX656                                                                        
20:200100O0012                                                                     
32A:010607USD6025,10                                                               
50:XYZ LABORATORIES PVT LTD|B/2 TESTCHAMBERS 22 B DESAI RD|CBD-26| -|||||||||||||  
59:W.S.A. TEXT SZCZESIUL|USA||                                                     
71A:BEN||||||


https://stewashton.wordpress.com/2016/08/01/splitting-strings-surprise/

If you want to keep the leading colon, you'll need to prepend it:

with d as (
select ('|100|BXX656|:20:200100O0012|:32A:010607USD6025,10|:50:XYZ LABORATORIES PVT LTD|B/2 TESTCHAMBERS 22 B DESAI RD|CBD-26| -||||||||||||||:59:W.S.A. TEXT SZCZESIUL|USA|||:71A:BEN||||||') d from dual
)
  select ':' || str from d, xmltable(
  'if (contains($X,"|:")) then ora:tokenize($X,"\|:") else $X'
  passing d as X
  columns str varchar2(4000) path '.'
  );

':'||STR                                                                            
:|100|BXX656                                                                        
:20:200100O0012                                                                     
:32A:010607USD6025,10                                                               
:50:XYZ LABORATORIES PVT LTD|B/2 TESTCHAMBERS 22 B DESAI RD|CBD-26| -|||||||||||||  
:59:W.S.A. TEXT SZCZESIUL|USA||                                                     
:71A:BEN||||||

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Interested and new to ora:tokenize

vino, October 24, 2016 - 9:36 am UTC

very very thanks to TOM
--
need more conditional as
if my input is 20 ,32A ,50 ,59 ,71A respective row need to fetch as

200100O0012
010607USD6025,10
XYZ LABORATORIES PVT LTD|B/2 TESTCHAMBERS 22 B DESAI RD|CBD-26| -|||||||||||||
W.S.A. TEXT SZCZESIUL|USA||
BEN||||||
Chris Saxon
October 24, 2016 - 12:18 pm UTC

Actually it's Chris!

if my input is 20 ,32A ,50 ,59 ,71A respective row need to fetch as

What exactly is your rule?

Those all appear with a colon after. You could just substr these out:

with d as (
select ('|100|BXX656|:20:200100O0012|:32A:010607USD6025,10|:50:XYZ LABORATORIES PVT LTD|B/2 TESTCHAMBERS 22 B DESAI RD|CBD-26| -||||||||||||||:59:W.S.A. TEXT SZCZESIUL|USA|||:71A:BEN||||||') d from dual
)
  select substr(str, instr(str, ':')+1) s
  from d, xmltable(
  'if (contains($X,"|:")) then ora:tokenize($X,"\|:") else $X'
  passing d as X
  columns str varchar2(4000) path '.'
  );

S                                                                               
|100|BXX656                                                                     
200100O0012                                                                     
010607USD6025,10                                                                
XYZ LABORATORIES PVT LTD|B/2 TESTCHAMBERS 22 B DESAI RD|CBD-26| -|||||||||||||  
W.S.A. TEXT SZCZESIUL|USA||                                                     
BEN||||||

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.