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