Network Programming in .NET

select convert(bigint,’6592370590..’) will fail with the error below due to the two dots at the end of the string, or a newline, or infact anything non-numeric.

Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to bigint.

Here’s a more brute-force way of converting ‘dirty’ varchars into numbers:

create function [dbo].[UDF_ExtractNumber](@NumStr varchar(100))
returns bigint
as
begin
BEGIN
WHILE PATINDEX(‘%[^0-9]%’,@NumStr)> 0
SET @NumStr = REPLACE(@NumStr,SUBSTRING(@NumStr,PATINDEX(‘%[^0-9]%’,@NumStr),1),”)
END
return convert(bigint,@NumStr)
end

This does have the side-effect of viewing complete nonsense as the number zero, but that’s fine for my application.