Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- -- =============================================
- -- Author: Josh Einstein
- -- Create date: 2012-06-27
- -- Description: Converts a string to Int64 and returns null if the conversion fails.
- -- I have tried pretty hard to ensure this can never cause an error. Let me know if
- -- I have missed something!
- -- =============================================
- CREATE FUNCTION [Utils].[TryParseInt64] (@Value nvarchar(255)) RETURNS bigint AS
- BEGIN
- IF @Value IS NULL RETURN NULL;
- SET @Value = LTRIM(RTRIM(@Value))
- -- Basic checks for way too long strings or empty strings
- IF LEN(@Value) = 0 OR LEN(@Value) > 38 RETURN NULL;
- -- Since ISNUMERIC returns true for exponent and decimal
- -- number formats, we will tack on a string that won't change
- -- the actual value but will make such strings malformed.
- -- example, if '1.5e15' is the string we'll check '1.5e15.0e0'
- -- which ISNUMERIC won't report as numeric.
- IF ISNUMERIC(@Value + '.0e0') = 0 RETURN NULL;
- -- Use numeric(38,0) because
- -- a) it's the most number of digits we can parse using built-in types
- -- b) we already verified the string contains no decimals
- -- Once we have a numeric type, we can do range checks for int type
- DECLARE @Result numeric(38,0) = CONVERT(numeric(38,0), @Value);
- IF @Result > 9223372036854775807 OR @Result < -9223372036854775808 RETURN NULL;
- RETURN CONVERT(bigint, @Result);
- END
Add Comment
Please, Sign In to add comment