Home
> Uncategorized > Parse a varchar to bigint using a regex (Regular Expression)
Parse a varchar to bigint using a regex (Regular Expression)
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.
Categories: Uncategorized
Comments (0)
Trackbacks (0)
Leave a comment
Trackback