SQL: how do I find if the contents of a varchar column are numeric?

oracle, sql

Solution

There is no native "isnumeric" function in oracle, but this link shows you how to make one: http://www.oracle.com/technetwork/issue-archive/o44asktom-089519.html

CREATE OR REPLACE FUNCTION isnumeric(p_string in varchar2)
RETURN BOOLEAN
AS
    l_number number;
BEGIN
    l_number := p_string;
    RETURN TRUE;
EXCEPTION
    WHEN OTHERS THEN
        RETURN FALSE;
END;
/

Problem

One of the columns in my table is a varchar that is supposed to contain only numeric values (I can't change the definition to a number column). Thus my SQL query: ``` select to_number(col1) from tbl1 where ... ``` fails because for some row the contents of the column are not numeric. What's a `select` query I can use to find these rows ? I'm using an Oracle database I'm looking for something like a `is_number` function.

Original source