ISNUMERIC() equivalent?

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!
LVL 1
dwalexAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
amitpagarwalConnect With a Mentor Commented:
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
 
bretCommented:
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
 
amitpagarwalCommented:
ya bret,

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

thanks a ton ..

0
 
dwalexAuthor Commented:
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
 
gardmanITCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.