New Faq's (IsNumeric, IsInt, IsNumber ) 1

[gmmastros]

I just created a new Faq's. Anyone care to review this for completeness and accuracy?

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[gmmastros]

faq183-6423

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLDenis]

fair enough

Insert Into @Temp Values(char(9))

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[jbenson001]

Could you put some explination in the FAQ about what the '.0e0' and '.e0' do?

Thanks..

Jim

 

[SQLDenis]

George try this

alter Function dbo.IsInteger(@Value VarChar(18))
Returns Bit
As
Begin
Return (Select case IsNumeric(ltrim((@Value)) + '.0e0')when '1' then
case when @Value between '-999999999999999999' and '999999999999999999' then 1 else 0 end else 0 end)
End

then run this

Declare @Temp Table(Data VarChar(18))

Insert Into @Temp Values('$12.09')
Insert Into @Temp Values('1.4e3')
Insert Into @Temp Values('2d4')
Insert Into @Temp Values('3.7')
Insert Into @Temp Values('412')
Insert Into @Temp Values('^')
Insert Into @Temp Values(char(9))

Select ltrim(data),Data,
IsNumeric(Data) As [IsNumeric],
dbo.IsInteger(Data) As IsInteger,
dbo.IsNumber(Data) As IsNumber
From @Temp


And I guess you can do the same for isNumeric

there are more ways to do this like '[0-9]' for example

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[gmmastros]

Denis

Thanks for the suggestion, I was thinking along these lines...

Alter Function dbo.IsInteger(@Value VarChar(18))
Returns Bit
As 
Begin
  Return IsNull((Select IsNumeric(@Value + '.0e0') where @Value Like '%[0-9]%'), 0)
End

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[gmmastros]

I modifed the faq for the IsInteger function and also explained the .0eo and e0 trick. This feedback is very helpful, thanks. Care to take another look?

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLDenis]

looks good

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[jbenson001]

Cool thanks

 

[SQLDenis]

George I don't know if this is nitpicking or not
But the value 412.00 is not returned as being an integer (which techically it is (I think))

Declare @Temp Table(Data VarChar(18))

Insert Into @Temp Values('$12.09')
Insert Into @Temp Values('1.4e3')
Insert Into @Temp Values('2d4')
Insert Into @Temp Values('3.7')
Insert Into @Temp Values('412.00') --Technically an int  ;-)

Select Data, 
       IsNumeric(Data) As [IsNumeric], 
       dbo.IsInteger(Data) As IsInteger, 
       dbo.IsNumber(Data) As IsNumber
From   @Temp

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[gmmastros]

ok. It gets messier (I think).

ALTER  Function dbo.IsInteger(@Value VarChar(18))
Returns Bit
As 
Begin
  
  Return IsNull(
     (Select Case When CharIndex('.', @Value) > 0 
                  Then Case When Convert(int, ParseName(@Value, 1)) <> 0
                            Then 0
                            Else 1
                            End
                  Else 1
                  End
      Where IsNumeric(@Value + 'e0') = 1), 0)


End

Declare @Temp Table(Data VarChar(18))

Insert Into @Temp Values('$12.09')
Insert Into @Temp Values('1.4e3')
Insert Into @Temp Values('2d4')
Insert Into @Temp Values('3.7')
Insert Into @Temp Values('412.00') --Technically an int  ;-)

Select Data, 
       IsNumeric(Data) As [IsNumeric], 
       dbo.IsInteger(Data) As IsInteger, 
       dbo.IsNumber(Data) As IsNumber
From   @Temp

Denis, I'd like for this to be as bullet proof as possible. Any suggestions are appreciated. Thanks.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLDenis]

another way

declare @s decimal(18,5)
select @s = 12
select case @s/convert(int,@s) when 1 then 'int' else 'no' end
GO

declare @s decimal(18,5)
select @s = 12.2
select case @s/convert(int,@s) when 1 then 'int' else 'no' end

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[gmmastros]

Denis,

I thought about converting it to a numeric data type but decided against it because we don't necessarily know what type of number it can convert to, or what the range of the number will. This is why I decided to check isnumeric(value + 'e0') and then use parsename.

Your thoughts?


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLDenis]

I think you took the right approach

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/