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.

Advertisements
Categories: Uncategorized
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: