Home » SQL & PL/SQL » SQL & PL/SQL » Query need
Query need [message #321166] Mon, 19 May 2008 05:27 Go to next message
balaji23_d
Messages: 123
Registered: February 2007
Senior Member
Hi,

How to find whether the given value is number or not
Re: Query need [message #321170 is a reply to message #321166] Mon, 19 May 2008 05:46 Go to previous messageGo to next message
Michel Cadot
Messages: 68704
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Nice title, very useful.
Nice question, has never been asked before.

Regards
Michel
Re: Query need [message #321171 is a reply to message #321166] Mon, 19 May 2008 05:46 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Do a TO_NUMBER on it, and catch the exceptions.
Re: Query need [message #321190 is a reply to message #321171] Mon, 19 May 2008 06:41 Go to previous messageGo to next message
skooman
Messages: 913
Registered: March 2005
Location: Netherlands
Senior Member
Or, if you don't want to use PL/SQL, just SQL do something like:

SELECT CASE
WHEN length(translate('abc123'
,'x1234567890.,'
,'x')) IS NULL THEN
'yes'
ELSE
'no'
END is_number
FROM dual
Re: Query need [message #321193 is a reply to message #321190] Mon, 19 May 2008 06:46 Go to previous messageGo to next message
Michel Cadot
Messages: 68704
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Sabine,

12..34.5 is not a valid number and will pass your test:
SQL> SELECT CASE
  2  WHEN length(translate('12..34.5', 'x1234567890.,'
  3  ,'x')) IS NULL THEN
  4  'yes'
  5  ELSE
  6  'no'
  7  END is_number
  8  FROM dual
  9  /
IS_
---
yes

1 row selected.

Regards
Michel
Re: Query need [message #321214 is a reply to message #321193] Mon, 19 May 2008 08:03 Go to previous message
skooman
Messages: 913
Registered: March 2005
Location: Netherlands
Senior Member
Oops...
Have to admit that the code was from a situation where no dots or commas were involved, I just added them on the fly. Stupid me...

Well, if you want just SQL and no dots and commas are involved, it works! Wink
Previous Topic: data encryption and decryption
Next Topic: Needed help for Auditing
Goto Forum:
  


Current Time: Sat Nov 02 13:09:17 CDT 2024