| How to Implement IsNumeris Function using Oracle [message #382618] |
Fri, 23 January 2009 04:14  |
sr_orcl
Messages: 82
Registered: January 2009
Location: mumbai
|
Member |
|
|
Hi,
Please suggest me how to implemnet Isnumeris function using SQL
I have search this query,
Test Case:
select instr(translate('%ddd11d0dd01ddd0',
'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'),'X')
FROM dual;
But I am not able to understand this ?why 'X' is used?
Can we implement using Regulat Expression??
|
|
|
|
|
|
|
|
| Re: How to Implement IsNumeris Function using Oracle [message #382634 is a reply to message #382618] |
Fri, 23 January 2009 05:10  |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
I don't know where you got that query from, but it won't tell you if an input string is a valid number.
What happens if you put the strings '100.001', '100..001' and '100.00.01' in to it?
You need a function like this:
CREATE OR REPLACE FUNCTION IS_NUMBER (p_string in varchar2) RETURN varchar2 AS
v_num number;
BEGIN
v_num := to_number(p_string);
return 'Y';
EXCEPTION
WHEN OTHERS THEN
return 'N';
END;
/ |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
