CODEWITHSUNDEEP
sqloracle-databaseplsqloracle11g
Devoloper250
Devoloper250

Reputation: 813

How to capture 20 digit numeric or floating number to number field in oracle

I got a requirement to validate the number field which can occupy 20 numeric digits or 16 numeric digits and 4 decimal point values maximum. And also + and - signs shouldn't be calculated in this 20 digits.

Examples:

  1. 12345678901234567890 - Pass
  2. +12345678901234567890 - Pass
  3. -12345678901234567890 - Pass
  4. 1234567890123456.7890 - Pass
  5. +1234567890123456.7890 - Pass
  6. -1234567890123456.7890 - Pass
  7. -12345678901234567.890 - Pass
  8. 123456789012345.67890 - Fail
  9. 123456789012345678901 - Fail
  10. 1234567890 - Pass
  11. 1.1 - Pass

Any thoughts how could we validate these kind of numbers?

Upvotes: 0

Views: 426

Answers (1)

Maheswaran Ravisankar
Maheswaran Ravisankar

Reputation: 17920

Function

Added the explanation as comments. You can the use the format model SFM99999999999999999990.9999 to validate your number. Exception raised would means, the input doesn't match the format.

create or replace function validateMe(str IN  VARCHAR2)
return
varchar2
as
STR_COPY VARCHAR2(4000);
RESULT NUMBER;
BEGIN
/* Prefix the Sign by default */
if(SUBSTR(str,1,1) NOT IN ('+','-')) THEN
STR_COPY := '+' || STR;
else
STR_COPY := STR;
end if;

/* Check if the length is > 21 -- Including the sign and excluding decimal */
if(length(REPLACE(STR_COPY,'.')) > 21 ) then
  return 'Fail';
end if;

/* Use the TO_NUMBER() function to validate */
RESULT := to_number(STR_COPY,'SFM99999999999999999990.9999');

RETURN 'Pass';
EXCEPTION WHEN OTHERS THEN
/* Exception means Failure */
  RETURN 'Fail';
END validateMe;
/

Query:

with mydata(str) as
(
select '12345678901234567890.56' from dual
union all
select '123456789012345678.56' from dual
union all
select '+12345678901234567890' from dual
union all
select '-12345678901234567890' from dual
union all
select '1234567890123456.7890' from dual
union all
select '+1234567890123456.7890' from dual
union all
select '-1234567890123456.7890' from dual
union all
select '-12345678901234567.890' from dual
union all
select '123456789012345.67890' from dual
union all
select '123456789012345678901' from dual
union all
select '1234567890' from dual
union all
select '1.1' from dual
)
select str,validateMe(str) from mydata;

Upvotes: 2

Related Questions