Home » SQL & PL/SQL » SQL & PL/SQL » How to Implement IsNumeris Function using Oracle (Oracle10G)
How to Implement IsNumeris Function using Oracle [message #382618] Fri, 23 January 2009 04:14 Go to next message
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 #382627 is a reply to message #382618] Fri, 23 January 2009 04:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Anything (but a figure) can be used.

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel
Re: How to Implement IsNumeris Function using Oracle [message #382628 is a reply to message #382618] Fri, 23 January 2009 04:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Can we implement using Regulat Expression??

Yes.
A little search in the forum will give you the answer.

Regards
Michel
Re: How to Implement IsNumeris Function using Oracle [message #382634 is a reply to message #382618] Fri, 23 January 2009 05:10 Go to previous message
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;
/
Previous Topic: To Call Procedure Name Based on the CURSOR VALUE..
Next Topic: Getting :NEW BLOB Value using AFTER INSERT Triggers
Goto Forum:
  


Current Time: Thu Dec 08 22:04:11 CST 2016

Total time taken to generate the page: 0.11996 seconds