Number filtering from Oracle with Decimal
Presanth Krishnan, July 01, 2016 - 6:42 am UTC
Thank you Let me try that
Don't expect too much
AndyP, July 01, 2016 - 8:00 am UTC
Just be careful, if adopting this approach, of assuming (as I did when virtual columns first appeared) that this would be a neat way to enforce numeric on insert into a varchar2 column. It kind of does, but probably not in a useful way:
create table t (user_input varchar2(20), x number(5,2) generated always as (to_number(user_input)));
insert into t (user_input) values ('9');
1 row created.
insert into t (user_input) values ('9.00');
1 row created.
select * from t;
USER_INPUT X
-------------------- ----------
9 9
9.00 9
insert into t (user_input) values ('non-numeric');
1 row created.
commit;
Commit complete.
select * from t;
ERROR:
ORA-01722: invalid number
no rows selected
Tom Style
Rajeshwaran, Jeyabal, July 01, 2016 - 12:39 pm UTC
Well, if this was Tom Kyte - He would have put two famous question, than providing this answer.
1) Why? why do you need 9 and 9.00 to appear to be different?
since you request for to_char(n,999.99) explicitly, even 9 would appear as 9.00, but that is correct, since you requested it explicitly.
2) It depends.
July 02, 2016 - 1:24 am UTC
Hence my emphasis:
If you *really* needed ... but does that seem a lot of work to me.