Home » SQL & PL/SQL » SQL & PL/SQL » A way to check if a string is infact a number
A way to check if a string is infact a number [message #272310] Thu, 04 October 2007 09:12 Go to next message
harveyb
Messages: 5
Registered: October 2007
Junior Member
Hi All

I need to be able to check to see if a string is actually a number

is there any function in plsql that can do this?

so basically i want to do

val:='123';

if is_number(val) then print "is number"
else print "not number"

would print out "is number" but if val was '123A' then it would print out "not number"

(i knot this isnt valid plsql its my pseudo code)

Is there any way of doing this? I know I "could" catch the conversion with to_number with an exception and handle it that way, but its a little bit messy to do it that way, especially if there is a function that does it.

Re: A way to check if a string is infact a number [message #272313 is a reply to message #272310] Thu, 04 October 2007 09:17 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Search on the board for "isnumber" or the like, you will find answers.
Or search on google, I posted such a function about 10 years ago on usenet. Many others posted the same kind of thing along these years.

Regards
Michel
Re: A way to check if a string is infact a number [message #272453 is a reply to message #272313] Fri, 05 October 2007 01:59 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If you feel like writing one yourself, the simplest approach is to perform a TO_NUMBER on the string, and then trap any exceptions raised.
Re: A way to check if a string is infact a number [message #272603 is a reply to message #272310] Fri, 05 October 2007 12:53 Go to previous message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
all good advice.

Currenlty Oracle SQL does not offer an "is_this_a_number" function so you must write your own. As was indicated, a simple function that does a to_number and either returns the result or null, is the best way to go.

Good luck, Kevin
Previous Topic: Select Query to display Aggregate functions for each row
Next Topic: Fetch out of sequence error.
Goto Forum:
  


Current Time: Sun Feb 09 22:35:59 CST 2025