asked on

Formatting varchar to numeric

Hi EEE

I'd like to change the varchar columns to number in my returned SQL in Oracle. How to? Tried

Select blah format s3
from myTable

ASKER CERTIFIED SOLUTION

Sean Stuber

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Geert G

if you want to find which ones can't be converted ...

select * from table
where not regexp_like(column, '\d')

SOLUTION

Helena Marková

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Mark Geerlings

Trying to do this: "change the varchar [values] to number in my returned SQL in Oracle" will usually result in this error:
ORA-01722: invalid number

Because, usually data that is in a VARCHAR2 column in Oracle is not all valid numbers, or is not all formatted consistently enough to automatically convert it to a NUMBER datatype. You will usually need something like the suggestion from Geert Gruwez to identify the records with "bad" values first. (I'm not an expert with "regular expressions" like what he suggested, but they can be handy. I usually have to use a PL\SQL function with a "to_number" conversion and an exception section.) Then, if you manually update any records with non-numeric data to valid, numeric values first, you should be able to run your query with TO_NUMBER.

SOLUTION

awking00

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Mark Geerlings

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial