Introduction/ Issue: Write the issue that you face or the issue that you want to provide a solution through this blog.
Zip code will be validated based on input parameters
Why we need to do / Cause of the issue: Write the details about the issue – how does it occur and what is the impact of it.
We can validate the correct zip code and avoid the characters and special characters.
How do we solve: Write the Query or the steps to solve the issue with your real-time example or scenarios. Note: do not mention any customer name / VPN details.
We can create one function in database and validate the zip code based on input parameters.
CREATE OR REPLACE FUNCTION FN_ZIPCODE_CHECK (ip_zip varchar2 ) return varchar2 IS
i number := 0 ;
lc_ret varchar2(50);
lc_zip_char varchar2(1):= ‘Y’ ;
ln_zip_cnt number(10) ;
lc_zip varchar2(10) ;
lc_zip1 varchar2(10) ;
lc_can_zip varchar2(1) ;
ln_space_cnt number := 0 ;
Begin
lc_zip := ltrim(rtrim(ip_zip)) ;
ln_zip_cnt := length(lc_zip );
for i in 1..ln_zip_cnt
loop
lc_zip_char := substr(lc_zip,i,1) ;
if ascii(lc_zip_char) = 32 then
ln_space_cnt := ln_space_cnt + 1 ;
if ln_space_cnt > 1 then
lc_ret := ‘Not Valid Zipcode’ ;
return lc_ret ;
end if;
elsif ascii(lc_zip_char) < 48 then
lc_ret := ‘Not Valid Zipcode’ ;
return lc_ret ;
elsif ascii(lc_zip_char) > 57 then
lc_ret := ‘Not Valid Zipcode’ ;
return lc_ret ;
end if ;
end loop;
if ln_zip_cnt = 6 then
lc_ret := ‘Valid Zipcode’ ;
return lc_ret ;
elsif ln_zip_cnt <> 6 then
lc_ret := ‘Not Valid Zipcode’;
return lc_ret ;
end if;
End;
Output:
Here we are passing the zip code and get the result for valid.
Here we are passing the zip code with character and get the result for not valid.
Conclusion: How did we provide an effective solution and resolve that issue/ unique solution given etc.
We can validate all the zip codes and check it is valid or not valid.
