Home » SQL & PL/SQL » SQL & PL/SQL » How to parse pieces of data from a Clob? (Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Windows Serv 2003)
How to parse pieces of data from a Clob? [message #572518] Wed, 12 December 2012 14:28 Go to next message
dhardy393
Messages: 2
Registered: December 2012
Location: San Antonio
Junior Member
I have a table with a clob field that has the format and similar data as below:

MSH^\|~&^ZZZS\LAFFFD^F2111^CIW\ORE^0073^20100924140100^^ORM\O01^F2111-99999^P^2.2^^^^MN^^
PID^1^^111115\\\F2111^20/999-99-9999^HAUUDY\DAN\F\\\^^19821031000000^M^^C^123 ANY ROAD\\ANY CITY\ANY STATE\12345\^^2282557551^^^M^CAT^^999-99-9999^^^9^^^^^4184\ABC\99PAT\N31\\99PTC^ ZCN^689828-02823^13^^PTC^^

I need to extract just 5 pieces of data from the clob into a different table; but will only know the patterns of the pieces of data and not the actual values.

1 - 111115
2 - 20/999-99-9999
3 - HAUUDY\DAN\F
4 - 23 ANY ROAD\\ANY CITY\ANY STATE\12345\
5 - 999-99-9999

I have the function below; can someone please explain how or if I can modify this function to extract my distinct pieces of data from the clob? Any assistance would be appreciated.

Thank you,


create table t ( id number, x clob );


insert into t (id,x) values ( 1,
2 'Hi i am SK142
3 I am also SK345
4 I am even SK346
5 SK444 is out of reach
6 This person SK567 is not valid');


create or replace function parse( p_clob in clob ) return
sys.odciVarchar2List
2 pipelined
3 as
4 l_offset number := 1;
5 l_clob clob := translate( p_clob, chr(13)|| chr(10) || chr(9), ' ' ) ||
' ';
6 l_end number;
7 l_hit number;
8 l_len number := dbms_lob.getlength( p_clob );
9 begin
10 loop
11 l_hit := instr( l_clob, 'SK', l_offset );
12 exit when nvl(l_hit,0) = 0;
13 l_end := instr( l_clob, ' ', l_hit );
14
15 pipe row ( substr( l_clob, l_hit, l_end-l_hit+1 ) );
16
17 l_offset := l_end;
18 end loop;
19 end;
20 /


select id, column_value from t, table(parse(t.x));

ID COLUMN_VAL
---------- ----------
1 SK142
1 SK345
1 SK346
1 SK444
1 SK567
2 SK142
2 SK346
2 SK444
2 SK567
Re: How to parse pieces of data from a Clob? [message #572543 is a reply to message #572518] Thu, 13 December 2012 01:35 Go to previous messageGo to next message
Michel Cadot
Messages: 58643
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
How can we know these are the 5 fields to extract?
Give the rules.

Regards
Michel
Re: How to parse pieces of data from a Clob? [message #572588 is a reply to message #572543] Thu, 13 December 2012 07:50 Go to previous messageGo to next message
dhardy393
Messages: 2
Registered: December 2012
Location: San Antonio
Junior Member
Please see below the rules that I've come up with.

Thank you,

MSH^\|~&^ZZZS\LAFFFD^F2111^CIW\ORE^0073^20100924140100^^ORM\O01^F2111-99999^P^2.2^^^^MN^^
PID^1^^111115\\\F2111^20/999-99-9999^HAUUDY\DAN\F\\\^^19821031000000^M^^C^123 ANY ROAD\\ANY CITY\ANY STATE\12345\^^2282557551^^^M^CAT^^999-99-9999^^^9^^^^^4184\ABC\99PAT\N31\\99PTC^ ZCN^689828-02823^13^^PTC^^

1 - 111115 - ID number: third piece from start of string which starts with PID; length of ID varies

2 - 20/999-99-9999 - Prefix/SSN: fourth piece from start of string which starts with PID; length does not vary; always same format

3 - HAUUDY\DAN\F - Name: fifth piece from start of string which starts with PID character; length of Name varies; Last character optional

4 - 23 ANY ROAD\\ANY CITY\ANY STATE\12345\ - Address: 10 piece from start of string which starts with PID; length varies

5 - 999-99-9999 - SSN: 18th piece from start of string which starts with PID; length does not vary; always same format.
Re: How to parse pieces of data from a Clob? [message #572594 is a reply to message #572588] Thu, 13 December 2012 08:26 Go to previous message
dariyoosh
Messages: 531
Registered: March 2009
Location: Iran / France
Senior Member
What do you use exactly as delimiter in the given string? Because here, you explain, third piece, fourth piece, etc. but comparing to what exactly you define the first, second, third,etc. tokens?

If there is a pattern which could be considered as a delimiter, then you can use REGEXP_SUBSTR


Regards,
Dariyoosh

[Updated on: Thu, 13 December 2012 08:30]

Report message to a moderator

Previous Topic: General dbms_job Question
Next Topic: How to get the file name from directory
Goto Forum:
  


Current Time: Fri Aug 01 20:45:15 CDT 2014

Total time taken to generate the page: 0.11644 seconds