Solved

ISNUMERIC() equivalent?

Posted on 2001-09-13
4
1,141 Views
Last Modified: 2012-05-04
Don't ask me why, but a colleague has a need for the equivalent of MS SQL Server's ISNUMERIC() function in Transact SQL. Is there a stored proc somewhere already that can accomplish the feat pretty quickly?

I'd search PAQ, but it doesn't seem to be working!
0
Comment
Question by:dwalex
4 Comments
 
LVL 5

Expert Comment

by:amitpagarwal
Comment Utility
I have written the following SP for sybase to check if a given string is Numeric. It returns 0 if numeric else -1.

You can modify it to have print statements also.

Thanks.

  create proc isNumber @value varchar(15)
as
begin
 declare @number_count int
 declare @decimal_count int
 select  @decimal_count = 0
 select  @number_count = char_length(@value)
  while (@number_count > 0)
   begin
    if(substring(@value,@number_count,1)
  IN ('0','1','2','3','4','5','6','7','8','9','.'))
     begin
      select @number_count = @number_count - 1
      if(substring(@value,@number_count,1) = '.')
       begin
        select @decimal_count = @decimal_count + 1
        if(@decimal_count > 1)
 
         return -1
       end
     end
   else return -1
  end
 return 0
end
0
 
LVL 3

Accepted Solution

by:
mathavra earned 100 total points
Comment Utility
Here is another method I have used

select patindex('%[^0-9]%', "123R23")
go
------------
           4

will return non-zero value if any of character is non-numeric. Else, it will return zero.

select patindex('%[^0-9]%', "123023")
go
------------
           0

You can use this function anywhere to check whether it returns zero or non-zero.

Hope this helps!

Mathav
0
 
LVL 10

Expert Comment

by:bret
Comment Utility
Neither of these techniques work for negative numbers, or for numbers in scientific notation (-3.456e5)

-bret
0
 
LVL 1

Author Comment

by:dwalex
Comment Utility
That's the kind of quick answer I was after. As bret says, it's not perfect, but for a financial application, it helps alot.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Marketing can be an uncomfortable undertaking, especially if your material is technology based. Luckily, we’ve compiled some simple and (relatively) painless tips to put an end to your trepidation and start your path to success.
HOW TO: Install and Configure VMware vSphere Hypervisor 6.5 (ESXi 6.5), Step by Step Tutorial with screenshots. From Download, Checking Media, to Completed Installation.
This video discusses moving either the default database or any database to a new volume.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now