Solved

ISNUMERIC() equivalent?

Posted on 2001-09-13
5
6,792 Views
Last Modified: 2008-02-26
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
5 Comments
 
LVL 5

Accepted Solution

by:
amitpagarwal earned 100 total points
ID: 6481656
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 10

Expert Comment

by:bret
ID: 6482806
Sorry for the unconstructive criticism, but this procedure...

doesn't seem to handle negative numbers  "-1.0"
doesn't seem to handle numbers in scientific notation "2.345e20" (positive or negative)

I'm not aware of anything better, though (unless one uses the server-side Java option in ASE 12.0 and 12.5).

-bret
0
 
LVL 5

Expert Comment

by:amitpagarwal
ID: 6482809
ya bret,

i agree with u .. i should include this in my code ..

thanks a ton ..

0
 
LVL 1

Author Comment

by:dwalex
ID: 6485326
It's sufficient for my purposes, amitpagarwal, thanks. I'll add the negative sign, and may have to add parentheses too, and maybe commas? This is for a financial application, and sometimes these things appear too, I'll wager.

I was hoping there was a somewhat more elegant way than the brute force method, but it seems unlikely. Anyone with a different answer can earn another hundred points though.
0
 
LVL 4

Expert Comment

by:gardmanIT
ID: 20849642
Just for info and based on the correct answer above
Here is the function convderted to AS400 SQL

I also changed it to return 1 if numeric and 0 if not as this suited me better.

Cheers,

CREATE FUNCTION libraryname.isNumeric (@value varchar (15)) RETURNS INTEGER LANGUAGE SQL 
 

BEGIN 
 

 declare @number_count int

;
 

 declare @decimal_count int;
 

 set  @decimal_count = 0

;
 

 set  @number_count = char_length(@value)

;
 

  while (@number_count > 0)
 

 DO
 

    if(substring(@value,@number_count,1)

 IN ('0','1','2','3','4','5','6','7','8','9','.'))

 Then
 

      set @number_count = @number_count - 1

;
 

      if(substring(@value,@number_count,1) = '.')

 Then
 

        set @decimal_count = @decimal_count + 1

;
 

        if(@decimal_count > 1)

 Then
 

 
 

         return 0

;
 

       end
 

 if;
 

     end if

;
 

   else return 0

;
 

   end if;
 

  end while;
 

 return 1;
 

end

;

Open in new window

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

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.
Cloud-based technologies and services will continue to grow in popularity in 2017 thanks to the simple, scalable and cost-effective solutions they deliver. Here are three areas where cloud adoption is poised to really take off.
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…

863 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

23 Experts available now in Live!

Get 1:1 Help Now