Q
Problem solve Get help with specific problems with your technologies, process and projects.

Numbers in a VARCHAR2 column

How do I query an Oracle table to find which rows contain numeric data in a column of type VARCHAR2?
See the earlier question on finding what rows contain valid dates for an explanation of the "run it up the flagpole and see who salutes" approach used in the function below.

CREATE OR REPLACE FUNCTION  to_num
(
    in_num_text  IN  VARCHAR2,
    in_fmt_text  IN  VARCHAR2
                     DEFAULT NULL
)
RETURN  NUMBER

--  *******************
--  **  t o _ n u m  **
--  *******************

--  to_num returns the value of in_num_text if it is a number in
--    the format specified by in_fmt_text, NULL if it is not.
--  If in_fmt_text is omitted, to_num will return a number if
--    in_num_text is a number in ANY usual format, e.g. '5', '+5',
--    '5.00', '.5e1' all return 5.

IS
BEGIN
    IF  in_fmt_text  IS NULL
    THEN
        RETURN  TO_NUMBER ( in_num_text );
    ELSE
        RETURN  TO_NUMBER ( in_num_text
                          , in_fmt_text
                          );
    END IF;
EXCEPTION
    WHEN OTHERS
    THEN
        RETURN  NULL;
END   to_num;
/

SELECT      '=>'          -- to make leading spaces clear
        ||  text
        ||  '<='
      , to_num (text)
      , to_num (text, '999.999')
FROM    number_test
ORDER BY
        text;

'=>'||TEXT||'<='         TO_NUM(TEXT) TO_NUM(TEXT,'999.999')
------------------------ ------------ ----------------------
=> 1 <=                             1
=>$10<=
=>+56<=                            56
=>2.3<=                           2.3                    2.3
=>4,000<=
=>5+6<=
=>7.888888<=                 7.888888
=>9.1e2<=                         910
=>MMIII<=
=><=

One important difference between TO_NUMBER and TO_DATE is that TO_DATE is always working with a particular format in mind. If you don't pass a format model to TO_DATE, it will use the system default. In other words, "SELECT TO_DATE (col_a) FROM table_x" will always result in an error if one row has '22-Dec-2003' and another has '2003-12-22' in col_a. TO_NUMBER, as shown above, is happy with such dissimilar formats as ' 1 ' and '9.1e2'.

Dig Deeper on Using Oracle PL-SQL

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close