Remove:
select TRANSLATE(’12A3′,‘0123456789’, ‘ ‘) FROM dual
or
select
LTRIM(RTRIM(TRANSLATE(UPPER(’12a’), ABCDEFGHIJKLMNOPQRSTUVWXYZ’, ‘ ‘))) as nr
, LTRIM(RTRIM(TRANSLATE(UPPER(’12a’),‘0123456789’, ‘ ‘))) as nr_add
from dual
Investigate:
declare
v1 VARCHAR2(200):= ‘ ‘ || CHR(10);
begin
dbms_output.put_line(‘————————- ‘);
dbms_output.put_line(TRANSLATE(v1
, ‘1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ ‘
,‘#####################################’));
dbms_output.put_line( TRANSLATE(v1, ‘ ‘, ‘#’));
IF
TRANSLATE(v1
,‘1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ ‘
,‘#####################################’)
= TRANSLATE(v1, ‘ ‘, ‘#’)
THEN
dbms_output.put_line(‘Only blanks ‘);
ELSE
dbms_output.put_line(‘Not only blanks’);
END IF;
END;
Or use some regular expressions (with oracle 10g)