Re: ** numeric check

From: Daniel Fink <Daniel.Fink_at_Sun.COM>
Date: Mon, 12 Jul 2004 15:52:06 -0600
Message-id: <40F30806.9050308@sun.com>

Here's a quick and dirty. Use wisely and at your own risk... SQL> desc dwf

  Name                                                                     Null?    Type
  ------------------------------------------------------------------------ -------- -------------------------------------------------
  COL1                                                                              VARCHAR2(10)



select col1,
        length(col1) orig_length,
        trim(translate(col1, '0123456789', '          ')) remove_nums,
        length(trim(translate(col1, '0123456789', '          '))) no_nums_length,
        case when length(trim(translate(col1, '0123456789', '          '))) > 0 then 'Non-numeric string'
             else 'Numeric String' end is_numeric

from dwf;

COL1 ORIG_LENGTH REMOVE_NUM NO_NUMS_LENGTH IS_NUMERIC

---------- ----------- ---------- -------------- ------------------
12345                5                           Numeric String
TEST                 4 TEST                    4 Non-numeric string
T12345               6 T                       1 Non-numeric string
      12345          10                           Numeric String

Regards,
Daniel

A Joshi wrote:
> Hi,
> I have a varchar2 column which contains some text and some totally numeric values. Is there an easy way to check if the value is totally numeric instead of looking at ascii values etc. Thanks for your help.
>
> ---------------------------------
> Do you Yahoo!?
> Yahoo! Mail - 50x more storage than other providers!
>
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to: oracle-l-request_at_freelists.org
> put 'unsubscribe' in the subject line.
> --
> Archives are at http://www.freelists.org/archives/oracle-l/
> FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------

---

Please see the official ORACLE-L FAQ: http://www.orafaq.com

---

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Mon Jul 12 2004 - 16:49:05 CDT