Finds only the numeric values from varchar2 columns
                                                 Last update (2008-02-17 17:14:51)
                                                                                                                   Date added (2008-02-17 16:50:53)

Summary
At the following example table Customers has a column B_NUMBER varchar2. In the column you can store mixed strings(only numbers or text with numbers). To find only the numeric records execute:
select * FROM Customers 
where NVL(LENGTH(REPLACE(TRANSLATE(B_NUMBER ,'0123456789' ,'000000000'),'0',NULL)),0)=0;

Other related queries is ISNUMBER, ISDATE functions. Don't you think Oracle would have made ISNUMBER, ISDATE, etc, other databases have these functions. Anyway, here is a "ISNUMBER" function you can use:
select instr(translate(column_name,
        'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'),'X')
FROM table_name;
It returns 0 if it is a number, 1 if it is not. This works if the field has only characters or digits. For example, having !!! as a string would not work in this case. If you care about other characters, change the translate to add those characters.

Here is another better way of doing tip ISNUMBER
select length(translate(trim(column_name),' +-.0123456789',' ')) from dual;
Will give you a zero if it is a number or greater than zero if not numeric (actually gives the count of non numeric characters)
Reviews
Filters
Search