CODEWITHSUNDEEP
stringoracle-databasedatecase
user5122076
user5122076

Reputation:

determine column having numeric or string in oracle

I need to find whether the value is numeric or not. For example i had a date value with data type as varchar2. I need to find the column returns only numeric or not. Need to handle this with case in oracle.

select case when d.data_type ='DATE' then to_char(to_date(v.value,'YYYYMMDD'),'DD-MM-YY') else v.value end as "Value" from data_type d , value v ;

In this example the v.value can contain string values. But i need to check whether the column having value as number in Case statement. Please help me on this. Expected query is 

select case when d.data_type ='DATE' and 'd.value haing number only' then to_char(to_date(v.value,'YYYYMMDD'),'DD-MM-YY') else v.value end as "Value" from data_type d , value v ;

Upvotes: 0

Views: 65

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270463

You can use regexp_like to see if the value has only numbers:

select (case when regexp_like(date, '[^0-9]') then 'NonNumeric'
             else 'Numeric'
        end)

Note: this checks only for digits. If you allow other characters(such as '-', '.', '+', ','), then the regular expression can be easily modified for that purpose.

Upvotes: 1

Related Questions