Oracle: Separating Numbers and Letters

Approach 2: Using Math

Adding an extra "and length(acctnum/1)=6" or similar operation has the desired effect of filtering 009341. A distinct disadvantage of the math approach is that when using it by itself, you will run into an error situation when trying to divide ‘A12345′ by one. 数据挖掘实验室

SQL> select ′009341′/1 from dual;

′009341′/1
----------
      9341

SQL> select ′A12345′/1 from dual;
select ′A12345′/1 from dual
       *
ERROR at line 1:
ORA-01722: invalid number

 
数据挖掘研究院
 

The math approach is still valid if you account for the ORA-01722 error, and one way to do this is to use a nested PL/SQL block within another PL/SQL block so you can deal with the exception without exiting the main processing loop.

数据挖掘研究院

SQL> set serveroutput on
SQL> declare
  2    v_result number;
  3    cursor c is
  4    select acctnum
  5    from account_test;
  6  begin
  7    for r in c loop
  8      begin
  9        select r.acctnum/1 into v_result from dual;
 10        dbms_output.put_line(r.acctnum||′ is a number′);
 11      exception
 12        when others then
 13          dbms_output.put_line(r.acctnum||′ is a NOT number′);
 14      end;
 15    end loop;
 16  end;
 17  /
A12345 is a NOT number
009341 is a number
783652 is a number

PL/SQL procedure successfully completed.

 数据挖掘研究院 

A slight modification to the select into v_result statement will perform the filtering operation from before, so 009341 will be flagged as not being a number (at least not in the sense of what we′re looking for).