Q

Parsing a database field and returning occurrences

I need to be able to parse an Oracle database field and return the number of occurrences of CR/LF {chr(10) and/or...

chr(13)} in the field as a number. You'll need to use the INSTR function to find the occurrences of CR/LF in the field. For example, let's use the following example:

SQL> create table t ( x varchar2(100) );
Table created.

SQL>
SQL> insert into t values ( 'Hello' || chr(10) || 'World' );

1 row created.

SQL> insert into t values ( 'Hello' || chr(13) || 'World' );

1 row created.

SQL> select x, instr(x,chr(10))
  2    from t
  3  /

X           INSTR(X,CHR(10)) INSTR(X,CHR(13))
----------- ---------------- ----------------
Hello                      6     0
World                        

Hello
World                      0     6

See how the INSTR function returns the first occurrence of the chr(10) or chr(13). Now, you'd need to create a function that could be applied to the field to count the occurrences:

create or replace function count_crlf ( p_str in varchar2 )
RETURN number
is
   l_str_cr   long := p_str ;
   l_str_lf   long := p_str ;
   n          number;
   ct_cr      number := 0;
   ct_lf      number := 0;
begin
    loop
       exit when l_str_lf is null;
       n := instr( l_str_lf, chr(10) );
       if n > 0 then
          ct_lf := ct_lf + 1 ;
       end if ;
       l_str_lf := substr( l_str_lf, n+1 );
    end loop;
    
    loop
       exit when l_str_cr is null;
       n := instr( l_str_cr, chr(13) );
       if n > 0 then
          ct_cr := ct_cr + 1 ;
       end if ;
       l_str_cr := substr( l_str_cr, n+1 );
     end loop;

    RETURN (ct_cr + ct_lf) ;
end;
/

I did each the counts for CR and LF separately... you could combine them into a single loop if you wanted or create two different functions if you need to have CR and LF totals returned separately rather than as a single total.

Then you'd simply call the function and pass it the column value:
SQL> select count_crlf(x)
  2    from t
  3  /

COUNT_CRLF(X)
-------------
1
1


SQL> insert into t values ( 'Hello' || chr(13) || 'World' || chr(10) || chr(13) );

1 row created.


SQL> select count_crlf(x)
  2    from t
  3  /

COUNT_CRLF(X)
-------------
1
1
3

This was last published in January 2004

Dig Deeper on Oracle database backup and recovery

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close