| How to parse pieces of data from a Clob? [message #572518] |
Wed, 12 December 2012 14:28  |
 |
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 #572594 is a reply to message #572588] |
Thu, 13 December 2012 08:26  |
 |
dariyoosh
Messages: 267
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 |
|
|
|
] 
Members
Search
Help
Register
Login
Home
