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
Join the conversationComment
Share
Comments
Results
Contribute to the conversation