William Robertson http://www.williamrobertson.net/code/utilities.pkg.txt 

1 Comments:

 sp said...

William,
You recently replied to my OTN posting regarding how to parse a text field with delimiters and Enclosures. The Split function worked find, but I needed to make some additional adjustments to handle.
1. Cases when the Enclosures is also within one of the delimiter data elments.
2. To handle Enclosures that are more then 1 digit in size.

Below is the revised code.
create or replace function Split( p_text VARCHAR2
,p_delimiter VARCHAR2 DEFAULT ','
,p_encloser VARCHAR2 DEFAULT NULL
) return VARCHAR2_TT Is
i PLS_INTEGER := 1;
v_next_pos PLS_INTEGER;
v_next_pos2 PLS_INTEGER;
v_encl_Len Pls_Integer := nvl(length(p_encloser),1);
v_enclosed BOOLEAN;

v_text VARCHAR2(2000) := LTRIM(RTRIM(p_text,p_delimiter),p_delimiter);
v_return_array VARCHAR2_TT := VARCHAR2_TT();

Begin
WHILE v_text IS NOT NULL LOOP
v_return_array.EXTEND;

-- Check whether (remaining) text starts with an encloser (e.g. doublequote):

v_enclosed := SUBSTR(v_text ,1,v_encl_Len) = p_encloser;

IF v_enclosed Then
v_text := TRIM(SUBSTR(v_text,v_encl_Len+1));
v_next_pos := nvl(INSTR(v_text,p_encloser||p_delimiter),0);
ELSE
v_next_pos := nvl(INSTR(v_text,p_delimiter),0);
END IF;

IF v_next_pos = 0 THEN
-- No more delimiters found, so return remaining text

--See if Encloser is also part of the text
v_next_pos2 :=nvl(INSTR(v_text,p_encloser||p_encloser),0);

If v_next_pos2 = 0 Then
-- (Strip off any spaces and encloser)
v_return_array(i) := RTRIM(v_text, p_encloser||' ');
Else
v_return_array(i) := substr(v_text,1,v_next_pos2);
End If;
EXIT;
ELSE
-- Use the portion of the text up to the next delimiter or encloser:
-- ('v_next_position' has already been worked out)
v_return_array(i) := TRIM(SUBSTR(v_text,1,v_next_pos -1));
v_text := TRIM(SUBSTR(v_text,v_next_pos+v_encl_len));

IF v_enclosed THEN
-- Deal with closing quote by stripping one more character:
v_text := TRIM(SUBSTR(v_text,2));
END IF;
END IF;
i := i +1;
END LOOP;

RETURN v_return_array;
end Split;

Tuesday, December 15, 2009 7:06:00 PM  

Post a Comment

Links to this post:

  <$BlogBacklinkTitle$>  

<$BlogBacklinkSnippet$>
posted by <$BlogBacklinkAuthor$> @ <$BlogBacklinkDateTime$>

Create a Link

<< Index of code examples