CODEWITHSUNDEEP
sqlstringoracle-databasewhere-clause
Sedi
Sedi

Reputation: 89

select only numeric values otherwise return NULL

I am quite new to Oracle.

I have a column in Oracle called Salary that has values like '19000.00', NULL, N/A, prefer not to say, '17500'.

How do I write a sql query that returns numerical values = no letters or null?

From the example above would be '19000.00', '17500.00'. Otherwise for all non-numeric values, including NULL and letters, returns NULL. For example for NULL, N/A, prefer not to say. 

select salary from myTable where ...?

Upvotes: 2

Views: 862

Answers (2)

Tony Andrews
Tony Andrews

Reputation: 132710

From Oracle 12.2 you can use VALIDATE_CONVERSION:

select salary
from my_table
where VALIDATE_CONVERSION(salary AS NUMBER) = 1;

Or to show NULLs for the non-numbers:

select case when VALIDATE_CONVERSION(salary AS NUMBER) = 1 then salary end 
from my_table;

Upvotes: 1

GMB
GMB

Reputation: 222672

You could simply use a regex to filter the table, like: REGEXP_LIKE(salary, '\d+(\.\d+)?')

This expression allows a sequence of digits, optionnaly followed by a dot and more digits.

Demo on DB Fiddle:

with x as (
    SELECT 'N/A' salary FROM DUAL
    UNION ALL SELECT NULL FROM DUAL
    UNION ALL SELECT '19000.00' FROM DUAL
    UNION ALL SELECT '17500' FROM DUAL
)
SELECT * FROM x WHERE REGEXP_LIKE(salary, '\d+(\.\d+)?')

Yields:

| SALARY   |
| :------- |
| 19000.00 |
| 17500    |

PS: if don't want to filter out the non-numeric values but replace them with NULL instead, then:

SELECT CASE WHEN REGEXP_LIKE(salary, '\d+(\.\d+)?') THEN salary END FROM x

Upvotes: 3

Related Questions