Numeric field

[mooks]

Hi

Is there any function to check that a value starts with numeric only? Thanks!

[hrishy]

Hi

You can try the to_number


SQL> select to_number('A') from dual;
select to_number('A') from dual
*
ERROR at line 1:
ORA-01722: invalid number


SQL> ed
Wrote file afiedt.buf

1* select to_number('10') from dual
SQL> /

TO_NUMBER('10')
---------------
10 function

1 declare
2 l_number number;
3 begin
4 select to_number('A') into l_number from dual;
5 exception
6 when invalid_number then
7 dbms_output.put_line('Invalid Number');
8* end;
SQL> /

PL/SQL procedure successfully completed.

SQL> set serveroutput on
SQL> /
Invalid Number

PL/SQL procedure successfully completed.


regards
Hrishy

[DaPi]

Try:
translate(substr(my_col,1,1),'1234567890','0000000000') = '0'

[oracle_faq]

create your own "is_number" function

create or replace function is_number (p_value in varchar2)
return boolean as
l_number number;
begin
l_number := p_value;
return true;
exception
when others then
return false;
end is_number;

[padders]

Several approaches spring to mind...

Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

SQL> SET SERVEROUTPUT ON;
SQL> DECLARE
  2     v_cnt   PLS_INTEGER   := 10 ** 5;
  3     v_tme   PLS_INTEGER   := 0;
  4     v_bln   BOOLEAN;
  5     v_str   VARCHAR2 (10) := '9ASDJADJ';
  6  BEGIN
  7     v_tme := DBMS_UTILITY.get_time;
  8  
  9     FOR i IN 1 .. v_cnt
 10     LOOP
 11        v_bln := ASCII (v_str) BETWEEN 48 AND 57;
 12     END LOOP;
 13  
 14     DBMS_OUTPUT.put_line ('[ASCII (v_str) BETWEEN 48 AND 57] ' || 
 15       (DBMS_UTILITY.get_time - v_tme) || ' hsecs.');
 16    
 17     v_tme := DBMS_UTILITY.get_time;
 18  
 19     FOR i IN 1 .. v_cnt
 20     LOOP
 21        v_bln := LPAD (v_str, 1) BETWEEN '0' AND '9';
 22     END LOOP;
 23  
 24     DBMS_OUTPUT.put_line ('[LPAD (v_str, 1) BETWEEN ''0'' AND ''9''] ' || 
 25       (DBMS_UTILITY.get_time - v_tme) || ' hsecs.');
 26    
 27     v_tme := DBMS_UTILITY.get_time;
 28  
 29     FOR i IN 1 .. v_cnt
 30     LOOP
 31        v_bln := CHR (ASCII (v_str)) BETWEEN '0' AND '9';
 32     END LOOP;
 33  
 34     DBMS_OUTPUT.put_line ('[CHR (ASCII (v_str)) BETWEEN ''0'' AND ''9''] ' || 
 35       (DBMS_UTILITY.get_time - v_tme) || ' hsecs.');
 36    
 37     v_tme := DBMS_UTILITY.get_time;
 38  
 39     FOR i IN 1 .. v_cnt
 40     LOOP
 41        v_bln := SUBSTR (v_str, 1) IN ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9');
 42     END LOOP;
 43  
 44     DBMS_OUTPUT.put_line ('[SUBSTR (v_str, 1) IN (''0'', ''1'', ''2'', ''3'', ''4'', ''5'', ''6', ''7'', ''8'', ''9'')] ' || 
 45       (DBMS_UTILITY.get_time - v_tme) || ' hsecs.');
 46    
 47     v_tme := DBMS_UTILITY.get_time;
 48  
 49     FOR i IN 1 .. v_cnt
 50     LOOP
 51        v_bln := SUBSTR (v_str, 1) BETWEEN '0' AND '9';
 52     END LOOP;
 53  
 54     DBMS_OUTPUT.put_line ('[SUBSTR (v_str, 1) BETWEEN ''0'' AND ''9''] ' || 
 55       (DBMS_UTILITY.get_time - v_tme) || ' hsecs.');
 56     
 57     v_tme := DBMS_UTILITY.get_time;
 58  
 59     FOR i IN 1 .. v_cnt
 60     LOOP
 61        v_bln := LTRIM (SUBSTR (v_str, 1, 1), '0123456789') IS NULL;
 62     END LOOP;
 63  
 64     DBMS_OUTPUT.put_line ('[LTRIM (SUBSTR (v_str, 1, 1), ''0123456789'') IS NULL] ' || 
 65       (DBMS_UTILITY.get_time - v_tme) || ' hsecs.');
 66     
 67     v_tme := DBMS_UTILITY.get_time;
 68  
 69     FOR i IN 1 .. v_cnt
 70     LOOP
 71        v_bln := LENGTH (LTRIM (v_str, '0123456789')) < LENGTH (v_str);
 72     END LOOP;
 73  
 74     DBMS_OUTPUT.put_line ('[LENGTH (LTRIM (v_str, ''0123456789'')) < LENGTH (v_str)] ' || 
 75       (DBMS_UTILITY.get_time - v_tme) || ' hsecs.');
 76    
 77     v_tme := DBMS_UTILITY.get_time;
 78  
 79     FOR i IN 1 .. v_cnt
 80     LOOP
 81        v_bln := TRANSLATE (SUBSTR (v_str, 1, 1), '1234567890', '0000000000') = '0';
 82     END LOOP;
 83  
 84     DBMS_OUTPUT.put_line ('[TRANSLATE (SUBSTR (v_str, 1, 1), ''1234567890'', ''0000000000'') = 
'0''] ' || 
 85       (DBMS_UTILITY.get_time - v_tme) || ' hsecs.');
 86  END;
 87  /

[ASCII (v_str) BETWEEN 48 AND 57] 26 hsecs.
[LPAD (v_str, 1) BETWEEN '0' AND '9'] 43 hsecs.
[CHR (ASCII (v_str)) BETWEEN '0' AND '9'] 44 hsecs.
[SUBSTR (v_str, 1) IN ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9')] 92 hsecs.
[SUBSTR (v_str, 1) BETWEEN '0' AND '9'] 42 hsecs.
[LTRIM (SUBSTR (v_str, 1, 1), '0123456789') IS NULL] 54 hsecs.
[LENGTH (LTRIM (v_str, '0123456789')) < LENGTH (v_str)] 45 hsecs.
[TRANSLATE (SUBSTR (v_str, 1, 1), '1234567890', '0000000000') = '0'] 303 hsecs.

PL/SQL procedure successfully completed.

SQL>

Looks like ASCII has it so far. Any other suggestions?

Padders

[DaPi]

Watch out!
substr('12345',1) = '12345' and ascii() is platform dependant.

I'd vote for:
SUBSTR (v_str, 1, 1) BETWEEN '0' AND '9'

[padders]

You're right about that Dapi. Actually I notice several mistakes in my script. So much for a quick test eh?

I tend to agree with your conclusion - it is not only quick but readable / intuitive etc.

Padders

[jmodic]

Originally posted by DaPi
Watch out!
substr('12345',1) = '12345' and ascii() is platform dependant.

I'd vote for:
SUBSTR (v_str, 1, 1) BETWEEN '0' AND '9'

Since quite a lot was said about performance, I woulkd further simplify the above suggestion to:

v_str BETWEEN '0' AND '9'

ommiting unneccesary SUBSTR() call.

[padders]

LOL. Good call.

Padders

[jmodic]

Originally posted by padders
LOL. Good call.

Actually not so good. I just realised I've made a stupid mistake with

v_str BETWEEN '0' AND '9'

since this will be false for any string beginning with '9' and followed by anything else. Since this is not what the original poster was looking for and since the character following "9" in any ASCII-based characterset is ":", my above suggestion should be rewritten into:

v_str >= '0' AND v_str < ':'

But then again, after reading this code only the most geekish of all the geeks will know what this code is all about, so I'd step back and vote for DaPi's suggestion (never mind the minor performance penalty) of using
SUBSTR (v_str, 1, 1) BETWEEN '0' AND '9'