How to test a column’s value numeric or not in Oracle table (ISNUMERIC equivalent for Oracle DB)


Use the following example SQL example for testing a column’s value is numeric or not using Regular Expression in Oracle

WITH TEST_TABLE
AS
(
SELECT * FROM (
SELECT ‘aa9’ AS TESTCOLUMN –> NUMERIC IS FALSE
FROM DUAL UNION
SELECT ‘abc’ –> NUMERIC IS FALSE
FROM DUAL UNION
SELECT ‘bcd12’ –> NUMERIC IS FALSE
FROM DUAL UNION
SELECT ‘001’ –> NUMERIC IS TRUE
FROM DUAL UNION
SELECT ‘123’ –> NUMBERIC IS TRUE
FROM DUAL UNION
SELECT ‘999’ –> NUMERIC IS TRUE
FROM DUAL
)
TABLEDUAL
ORDER BY TESTCOLUMN
)
SELECT  TEST_TABLE.TESTCOLUMN,
CASE WHEN regexp_like(TEST_TABLE.TESTCOLUMN, ‘^-?[[:digit:],.]*$’THEN
   ‘NUMERIC IS TRUE’
ELSE
‘NUMERIC IS FALSE’
END AS NUMERIC_OR_NOT
FROM TEST_TABLE;

Output for the SQL;

TESTCOLUMN NUMERIC_OR_NOT
aa9 NUMERIC IS FALSE
abc NUMERIC IS FALSE
bcd12 NUMERIC IS FALSE
001 NUMERIC IS TRUE
123 NUMERIC IS TRUE
999 NUMERIC IS TRUE
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s