Convert/cast varchar as int 1

[OhioSteve]

I am writing a function that has a varchar(100) parameter. I want to use it as a bigint.

1. Before converting the value, I want to know whether I can convert it. I know that isDate(expr) returns true if a value is a date. Does something similar exist for integers?

2. How do I actually do the conversion? I know that I should use either "cast" or "convert", but I do not recall the exact syntax.

Thanks alot for your input!

 

[bborissov]

SELECT CASE WHEN ISNUMERIC(MyField) = 0
                 Then 0
            WHEN CHARINDEX('e',MyField) > 0
                 THEN ????? -- for values like '1e2' and '5e7'
                            -- ISNUMERIC returns 1
                            -- you must deside what you will
                            -- do with such values
            ELSE CONVERT(bigint, MyField) END AS MyFieldInt

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[OhioSteve]

Okay I tried isNumeric(expr). It returns 1 even if the number is float. I want to screen out everything except whole positive integers.

 

[gmmastros]

Take a look at this faq183-6423.

-George

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

 

[OhioSteve]

Thanks for bringing that thread to my attention. It inspired me to write this function:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER Function [dbo].[isPosInt](@Value VarChar(20))
Returns Bit
As
Begin
return IsNumeric('-$'+@Value + '.0')
End

Here is an example of the function in action:
value isPosInt(value)
a False
1.2 False
-1 False
2 True
$1 False

 

[OhioSteve]

Okay, now I have a function for testing input. Let's assume that my parameters pass that test. So how do I say "take that varchar and put it into this bigint?" Here is the whole syntax~

ALTER FUNCTION [dbo].[myFunction]
( @a varchar(20), @b varchar(20))
RETURNS bigint
AS
BEGIN

declare @minInt bigint
declare @maxInt bigint

if dbo.isPosInt(@a)=0
begin
return -2;
end
if dbo.isPosInt(@b)=0
begin
return -2;
end

@minInt=cast(@a as bigint)????

 

[gmmastros]

Set @minInt=cast(@a as bigint)

Or

Set @MinInt = Convert(BigInt, @a)


-George

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

 

[OhioSteve]

Thanks gmmastros, you get a star