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
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 25th 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:
In fact, the ASPFAQ site is practically overflowing with good advice on ASP, SQL Server, and much more. So, go ahead and learn!