Fail-safe conversion to integer

The following function provides a simple solution to string-to-integer conversion.

It converts strings of data type nvarchar(4000) to numbers of data type integer.

By utilizing pattern-matching numeric characters are parsed from the input string from left to right until the pattern is matched. For instance: string "338/I" is converted to number 338. Before actual conversion, the part of the string to be converted is tested for compatibility using the isnumeric() system function.

NB (October 25^th 2007):

Razvan Socol has spotted a flaw in the original function, so thanks to him here is a safer version:

create function dbo.fnGet_asInteger
 (
 @String  nvarchar(4000)
 ,@Pattern nvarchar(4000)  = null
 )
returns int
as
begin
 declare @Result   int
 declare @intermediateResult nvarchar(4000)

 if (@Pattern is null)
  begin
   set @Pattern = '%[^0-9]%'
  end

 set @intermediateResult
   = case
    when @String like @Pattern
     then substring(@String, 1, patindex(@Pattern, @String) - 1)
    else @String
    end

 if (isnumeric(@intermediateResult) = 1)
  begin
   set @Result = cast(@intermediateResult as int)
  end
 else
  begin
   set @Result = null
  end

 return @Result
end
go

However, if you're looking for a much more resilient solution you might find this article very useful:

• What is wrong with IsNumeric()?
.

In fact, the ASPFAQ site is practically overflowing with good advice on ASP, SQL Server, and much more. So, go ahead and learn!

ML