Solved

ISNUMERIC() equivalent?

Posted on 2001-09-13
4
1,142 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
ID: 6481657
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
ID: 6488444
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
ID: 6488861
Neither of these techniques work for negative numbers, or for numbers in scientific notation (-3.456e5)

-bret
0
 
LVL 1

Author Comment

by:dwalex
ID: 6489244
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

There are many Password Managers (PM) out there to choose from. PM's can help with your password habits and routines, but they should not be a crutch you rely on too heavily. I also have an article for company/enterprise PM's.
In 2017, ransomware will become so virulent and widespread that if you aren’t a victim yourself, you will know someone who is.
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…

911 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

20 Experts available now in Live!

Get 1:1 Help Now