Numeric field
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Numeric field

  1. #1
    Join Date
    Jan 2000
    Posts
    387

    Numeric field

    Hi

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

  2. #2
    Join Date
    Jan 2001
    Posts
    2,828
    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
    Last edited by hrishy; 02-24-2004 at 06:48 AM.

  3. #3
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Try:
    translate(substr(my_col,1,1),'1234567890','0000000000') = '0'

  4. #4
    Join Date
    Feb 2004
    Posts
    77
    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;

  5. #5
    Join Date
    Jan 2004
    Posts
    162
    Several approaches spring to mind...
    Code:
    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

  6. #6
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Watch out!
    substr('12345',1) = '12345' and ascii() is platform dependant.

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

  7. #7
    Join Date
    Jan 2004
    Posts
    162
    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

  8. #8
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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:
    Code:
    v_str BETWEEN '0' AND '9'
    ommiting unneccesary SUBSTR() call.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  9. #9
    Join Date
    Jan 2004
    Posts
    162
    LOL. Good call.

    Padders

  10. #10
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by padders
    LOL. Good call.
    Actually not so good. I just realised I've made a stupid mistake with
    Code:
    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:
    Code:
    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'
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width