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 first published in January 2004

Dig deeper on Oracle database backup and recovery

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

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.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close