Sign Up to our social questions and Answers to ask questions, answer people’s questions, and connect with other people.
Login to our social questions & Answers to ask questions, answer people’s questions & connect with other people.
Lost your password? Please enter your email address. You will receive a link and will create a new password via email.
Please briefly explain why you feel this question should be reported.
Please briefly explain why you feel this answer should be reported.
Please briefly explain why you feel this user should be reported.
hello
I want to know if my column value is number or character in oracle 10g something like :
select (case when ( is_number(mycolumn))
then 'your column value is number'
else 'your column value is not number'
end)
from table myTable
thanks in advance.
one example to create a function to return ‘Y’ if parameter value is number otherwise which is exception in this case to return (‘N’)
ex:
[code]CREATE OR REPLACE FUNCTION is_number (p_string IN VARCHAR2)
RETURN VARCHAR2
DETERMINISTIC
PARALLEL_ENABLE
IS
l_num NUMBER;
BEGIN
l_num := TO_NUMBER (p_string);
RETURN ‘Y’;
EXCEPTION
WHEN VALUE_ERROR
THEN
RETURN ‘N’;
END is_number;[/code]
and here you can call the function created above to identify the value passed is number or not like :
[code]SELECT (CASE
WHEN (is_number (mycolumn) = ‘Y’)
THEN
‘your column value is number’
ELSE
‘your column value is not number’
END)
FROM myTable;[/code]
hope this help you.