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.
Thanks & Regards,
263 total views, 2 views today