Home » SQL & PL/SQL » SQL & PL/SQL » better way of string parsing?
better way of string parsing? [message #264896] Tue, 04 September 2007 23:30 Go to next message
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 #264897 is a reply to message #264896] Tue, 04 September 2007 23:39 Go to previous messageGo to next message
BlackSwan
Messages: 25038
Registered: January 2009
Location: SoCal
Senior Member
>Is there a better way of parsing the following string parsing rules..
NEVER use strings to store date datatypes.
Design to 3rd Normal form & NEVER store multiple values in a single field.
Re: better way of string parsing? [message #264974 is a reply to message #264897] Wed, 05 September 2007 03:03 Go to previous messageGo to next message
rleishman
Messages: 3724
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 #265226 is a reply to message #264974] Wed, 05 September 2007 16:34 Go to previous messageGo to next message
yog_23
Messages: 79
Registered: March 2007
Member
thank you...
Re: better way of string parsing? [message #265429 is a reply to message #265226] Thu, 06 September 2007 05:32 Go to previous messageGo to next message
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
Re: better way of string parsing? [message #265433 is a reply to message #265429] Thu, 06 September 2007 05:37 Go to previous message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Nice one. ./fa/2115/0/

Regards
Michel
Previous Topic: Randomly failing procedure
Next Topic: Find time difference
Goto Forum:
  


Current Time: Mon Dec 05 13:11:26 CST 2016

Total time taken to generate the page: 0.10952 seconds