14 Replies Latest reply: Jan 16, 2009 10:08 AM by BluShadow RSS

    IS_NUMBER function?

    493358
      I wrote a very primitive function to check if the contents of a VARCHAR field is Numeric. The function simply checks if every character in the field is between 0 - 9.

      I feel like this is a very inefficient way to check for numeric/non-numeric data.

      Is there a better way to do it?
        • 1. Re: IS_NUMBER function?
          121011
          CREATE OR REPLACE function IS_NUMBER(str in varchar2) return varchar2 IS
          dummy number;
          begin
          dummy := TO_NUMBER(str);
          return ('TRUE');
          Exception WHEN OTHERS then
          return ('FALSE');
          end;
          • 2. Re: IS_NUMBER function?
            493358
            So simple, I didnt think of catching Exception. Can you tell I am a newbie?

            Thank you very much!
            • 3. Re: IS_NUMBER function?
              Avinash Tripathi
              Hi,
              My solution might not be an efficient way to check the number. But you can use it without writing a function.

              1 select
              2 DECODE(replace(translate('12123','1234567890','##########'),'#'),NULL,'NUMBER','NON NUMER')
              3* from dual
              SQL>/

              DECODE
              ------
              NUMBER

              1 row selected.

              1 select
              2 DECODE(replace(translate('xyz12cv123abc','1234567890','##########'),'#'),NULL,'NUMBER','NON NUMER')
              3* from dual
              SQL>/

              DECODE(RE
              ---------
              NON NUMER

              1 row selected.



              Regards
              • 4. Re: IS_NUMBER function?
                495612
                SQL>create or replace function is_num(n varchar2)
                return varchar2 is
                begin
                for v in 1..length(n) loop
                if not((ascii(substr(n,v,1))>=48) and (ascii(substr(n,v,1))<=57)) then
                return 'F';
                end if;
                end loop;
                return 'T';
                end;
                /
                SQL> select is_num('25') from dual; -----It returns T
                SQL> select is_num('2A4R') from dual; -- it returns F

                Try it --- Jameel
                • 5. Re: IS_NUMBER function?
                  121011
                  SQL> select
                    2  DECODE(replace(translate('-12123','1234567890','##########'),'#'),NULL,'NUMBER','NON NUMER')
                    3  from dual;

                  DECODE(RE
                  ---------
                  NON NUMER

                  SQL> select
                    2  DECODE(replace(translate('12,123','1234567890','##########'),'#'),NULL,'NUMBER','NON NUMER')
                    3  from dual;

                  DECODE(RE
                  ---------
                  NON NUMER

                  SQL> 
                  • 6. Re: IS_NUMBER function?
                    SHUBH
                    http://www.oracle.com/technology/oramag/oracle/04-jul/o44asktom.html

                    this will give u a better understanding
                    cheers
                    SHUBH
                    • 7. Re: IS_NUMBER function?
                      Avinash Tripathi
                      Hi,
                      Sorry for not considerinf . , and - .

                      You can include any character as part of number


                      SQL>select
                      2 DECODE(replace(translate('-.,12123','-.,1234567890','#############'),'#'),NULL,'NUMBER','NON NUMER')
                      3 from dual;

                      DECODE
                      ------
                      NUMBER

                      1 row selected.

                      SQL>select
                      2 DECODE(replace(translate('-.,XX12123','-.,1234567890','#############'),'#'),NULL,'NUMBER','NON NUMER')
                      3 from dual;

                      DECODE(RE
                      ---------
                      NON NUMER

                      1 row selected.

                      Regards
                      • 8. Re: IS_NUMBER function?
                        21205
                        Are you saying that this -.,12123 is numeric?
                        I don't think so...
                        SQL> create table t
                          2  (x int);

                        Table created.

                        SQL> insert into t values (-.,12123);
                        insert into t values (-.,12123)
                                               *
                        ERROR at line 1:
                        ORA-00936: missing expression
                        • 9. Re: IS_NUMBER function?
                          APC
                          Are you saying that this -.,12123 is numeric?
                          Not to mention the fact that IP addresses aren't numeric, despite consisting wholly of digits and points (255.255.255.0).

                          This comes up from time to time and the easiest way of determining whether some string is numeric is to write a function to trap the ORA-1722 (or whatever) exception. If performance is a real issue we can always use native compilation. Although by now Oracle really ought to provide these things as built-ins. I guess it's that old problem about SQL not supporting BOOLEAN datatypes again.

                          Cheers, APC
                          • 10. Re: IS_NUMBER function?
                            Kaushik_Orcl
                            I know its kind of late to reply here... but here's another way to do it (10g though):

                            select decode(REGEXP_INSTR ('1234', '[^[:digit:]]'),0,'NUMBER','NOT_NUMBER') from dual;
                            -- Returns NUMBER

                            select decode(REGEXP_INSTR ('12-34', '[^[:digit:]]'),0,'NUMBER','NOT_NUMBER') from dual;
                            -- Returns NOT_NUMBER
                            • 11. Re: IS_NUMBER function?
                              SomeoneElse
                              select decode(REGEXP_INSTR ('1234', '[^:digit:]'),0,'NUMBER','NOT_NUMBER') from dual;
                              So, 12.34 isn't a number?
                              SQL> select decode(REGEXP_INSTR ('12.34', '[^:digit:]'),0,'NUMBER','NOT_NUMBER') from dual;
                              
                              DECODE(REG
                              ----------
                              NOT_NUMBER
                              • 12. Re: IS_NUMBER function?
                                AndyKlock
                                I think there is a small bug in the regular expression example in general. Not only is 12.34 not a number, but neither is 1234.

                                SQL>select decode(REGEXP_INSTR ('1234', '[^:digit:]'),0,'NUMBER','NOT_NUMBER') f
                                rom dual;

                                DECODE(REG
                                ----------
                                NOT_NUMBER

                                To use regular expressions in this way we need an extra bracket to search for any "non digits".

                                SQL>select decode(REGEXP_INSTR ('1234', '[^[:digit:]]'),0,'NUMBER','NOT_NUMBER') from dual;
                                DECODE
                                ------
                                NUMBER

                                SQL>select decode(REGEXP_INSTR ('12-34', '[^[:digit:]]'),0,'NUMBER','NOT_NUMBER'
                                ) from dual;

                                DECODE(REG
                                ----------
                                NOT_NUMBER

                                However, we still have the issue that SomeOne else brings up. This approach will only work on positive integers.

                                I have to say that Avinash's approach with translate, replace, and decode was a fun one to pick through.
                                • 13. Re: IS_NUMBER function?
                                  BluShadow
                                  The problem is not in that members regular expression but in the way the forum treats square brackets if you don't include {noformat}
                                  {noformat} tags around the code.
                                  
                                  The square brackets are in the code, you just can't see them because of the forum if the code hasn't been formatted.  (just as it's done with your code)  ;)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
                                  • 14. Re: IS_NUMBER function?
                                    BluShadow
                                    What Kaushik_Orcl had actually posted was...
                                    select decode(REGEXP_INSTR ('1234', '[^[:digit:]]'),0,'NUMBER','NOT_NUMBER') from dual;
                                    -- Returns NUMBER
                                    
                                    select decode(REGEXP_INSTR ('12-34', '[^[:digit:]]'),0,'NUMBER','NOT_NUMBER') from dual;
                                    -- Returns NOT_NUMBER
                                    Which you would see if you reply to his post and quote his text.