| better way of string parsing? [message #264896] |
Tue, 04 September 2007 23:30  |
yog_23
Messages: 79
Registered: March 2007
|
Member |
|
|
Is there a better way of parsing the following string parsing rules..
I/P:
String looking like
05/2006 - 08/2006;03/2007 - 07/2007;05/2007 - 09/2007;04/2007 - 10/2007;
Rules:
1. replace hyphen with 'to'
2. if from_date is 05/2006 then use 05/16/2006 else MM/01/YYYY
3. To_date: format to MM/XX/YYYY where XX-Last day of month
O/P should look like
[code]
05/16/2006 to 08/31/2006 , 03/01/2007 to 07/31/2007 , 05/01/2007 to 09/30/2007 , 04/01/2007 to 10/31/2007
[/code]
Current function is below:
create or replace function fnDateRules
(
p_list varchar2,
p_del varchar2 := ';',
p_del2 varchar2 := '-'
) return p_result
is
l_idx pls_integer;
l_idx1 pls_integer;
l_list varchar2(32767) := p_list;
l_value varchar2(32767);
l_data varchar2(32767);
l_data1 varchar2(32767);
l_data2 varchar2(32767);
l_data3 varchar2(32767);
l_data4 varchar2(32767);
l_final varchar2(32767);
begin
l_final := '';
loop
l_idx := 0;
l_idx := instr(l_list,p_del);
if l_idx > 0 then
l_data := substr(l_list,1,l_idx-1);
l_idx1 := 0;
l_idx1 := instr(l_data,p_del2);
if l_idx1 > 0 then
l_data1 := substr(l_data,1,l_idx1-1);
if trim(l_data1) = '05/2006' then
l_data1 := '05/16/2006';
else
l_data1 := substr(trim(l_data1),1,2) + '/01/' + substr(trim(l_data1),4,4);
end if;
l_data2 := substr(l_data,l_idx1 + 1, length(l_data) - l_idx1);
l_data3 := substr(trim(l_data2),1,2) + '/01/' + substr(trim(l_data2),4,4);
l_data4 := TO_CHAR(LAST_DAY(TO_DATE(l_data3,'MM/DD/YYYY')),'MM/DD/YYYY');
l_final := l_final + l_data1 + ' to ' + l_data4 + ',';
end if;
l_list := substr(l_list,l_idx+length(p_del));
else
exit;
end if;
end loop;
p_result := l_final ;
return;
end fnDateRules;
/
Thank you
Yog
|
|
|
|
|
|
| Re: better way of string parsing? [message #264974 is a reply to message #264897] |
Wed, 05 September 2007 03:03  |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
If the data is coming from somewhere outside of your control and you are sending it to an interface that is specifying a particular format, then your solution is about right. I didn't look closely, but you are not doing anything silly.
However if you are STORING this data, why wouldn't you normalise it? Surely someone is going to want to get it out of the database again and not necessarily in your chosen format.
Ross Leishman
|
|
|
|
|
|
| Re: better way of string parsing? [message #265429 is a reply to message #265226] |
Thu, 06 September 2007 05:32 |
Gary Revell
Messages: 6
Registered: August 2007
|
Junior Member |
|
|
Hi,
Have a look at the REGEXP_SUBSTR function as it could save you some time.
A little example below on V10.2.0.1
DECLARE
str varchar2(100);
src varchar2(200) := '05/2006 - 08/2006;03/2007 - 07/2007;05/2007 - 09/2007;04/2007 - 10/2007;';
item PLS_INTEGER:=1;
BEGIN
LOOP
str := regexp_substr(src ,'[^;]+',1,item);
EXIT WHEN str IS NULL;
dbms_output.put_line(item||' - '||str );
item := item + 1;
END LOOP;
END;
Best regards
Gary
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
