Ask the Expert

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.

    Requires Free Membership to View

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
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
Sort by: OldestNewest

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: