Requirement: I want to validate if the user-provided value is in Numeric format.

Solution: For the solution, of the above requirement let us create an Oracle Function is_number. The complete code for the is_number is as below:

CREATE OR REPLACE FUNCTION is_number (
     p_string IN VARCHAR2
 ) RETURN VARCHAR2 IS
     v_new_num NUMBER;
 BEGIN
     v_new_num := to_number(p_string);
     RETURN 'Provided value is numeric';
 EXCEPTION
     WHEN no_data_found THEN
         RETURN 'Provided value is non-numeric';
     WHEN OTHERS THEN
         RETURN 'Provided value is non-numeric';
 END;

Now, let us validate the result of the above function.

For that let us first pass the value as or “101.1“, or “101”, and check the result.

SELECT is_number('101.1') result FROM dual;

SELECT is_number('101') result FROM dual;

And the result of one of the above SQL Query is as below:

Now, let us first pass the value as “101SusantoTech”, and check the result.

SELECT is_number('101SusantoTech') result FROM dual;

And the result of the above SQL Query is as below:

Hence, the solution to our requirement.

If you like the post please comment, share, and do join me on Facebook. Please subscribe to my YouTube Channel for video tutorials.

Thanks & Regards,
Susanto Paul

 645 total views,  1 views today