TO_CHAR function & ORA-01722: invalid number error


I receive ORA-01722: invalid number error when running this query.

select TO_CHAR(PRSPRSC/100/2,'fm999.00') NOW_PRICE from prs;

What is the best way around this?
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
>> What is the best way around this?

Is PRSPRSC a number data type or a varchar2 that has numbers in it?

If a varchar2, the best way to fix it is fix the 'bad' data that is likely causing the problem.

The other choice is to write your own to_number function that can deal with non-numeric data the way you want to handle it and use that in the call.

If you create my_to_number then:

You would need to decide on what to return if the data is not a number.
xbox360dpAuthor Commented:
PRSPRSC is a varchar2 that has numbers in it.
slightwv (䄆 Netminder) Commented:
>>PRSPRSC is a varchar2 that has numbers in it.

then see the recommendations above:
1: Fix the data.
2: Write your own function to handle the 'bad' data.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

I agree, writing your own function would probably be best, but as a temporary measure ,you could try a case.

TO_CHAR(case when regexp_like(PRSPRSC,'^[0-9]*\.?[0-9]+$') then PRSPRSC/100/2 end,'fm999.00')

this will cause your non-numeric strings to return null

alternately, put the conversion inside the case, and return the original string when it's not valid so it's easily visible

           WHEN REGEXP_LIKE(prsprsc, '^[0-9]*\.?[0-9]+$') THEN TO_CHAR(prsprsc / 100 / 2, 'fm999.00')
           ELSE prsprsc

note, these regexps aren't guaranteed to capture all weird data entry, but will probably work for most.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
As for a custom function, I would put all of the formatting into that rather than converting to number and then formatting.  That way you can encapsulate all of the rules nicely in one place.

    v_temp NUMBER;
    v_temp := TO_NUMBER(p_str);

    -- If a format is given use it, otherwise use the default format for numbers
    IF p_format IS NOT NULL
        RETURN TO_CHAR(v_temp, p_format);
        RETURN TO_CHAR(v_temp);
    END IF;
        RETURN p_str;  -- change this to whatever you want non-numerics to do.

Open in new window

and then usage would look like this...

select formatter(PRSPRSC/100/2,'fm999.00') NOW_PRICE from prs;
slightwv (䄆 Netminder) Commented:
The accepted solution is a temporary fix at best and as noted, has potential flaws moving forward in someone enters something that is 'close' to a number but not exactly a number.

If they do that, you are right back where you are now.

Something is allowing 'bad' data into your tables and can lead to very bad results depending on the application.

First thing you really need to do is stop storing numbers in a varchar2 column.  Varchar2 is for strings.  number is for numbers.

Since you likely cannot do that, I would look at a job/script that at least monitors the table for 'bad' data daily/weekly and fix it as it comes in.

Better approach is what I mentioned and sdstuber posted: your own function.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.