Guest User

Untitled

a guest
Nov 19th, 2018
95
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 1.34 KB | None | 0 0
  1. -- =============================================
  2. -- Author: Josh Einstein
  3. -- Create date: 2012-06-27
  4. -- Description: Converts a string to Int64 and returns null if the conversion fails.
  5. -- I have tried pretty hard to ensure this can never cause an error. Let me know if
  6. -- I have missed something!
  7. -- =============================================
  8. CREATE FUNCTION [Utils].[TryParseInt64] (@Value nvarchar(255)) RETURNS bigint AS
  9. BEGIN
  10.  
  11. IF @Value IS NULL RETURN NULL;
  12.  
  13. SET @Value = LTRIM(RTRIM(@Value))
  14.  
  15. -- Basic checks for way too long strings or empty strings
  16. IF LEN(@Value) = 0 OR LEN(@Value) > 38 RETURN NULL;
  17.  
  18. -- Since ISNUMERIC returns true for exponent and decimal
  19. -- number formats, we will tack on a string that won't change
  20. -- the actual value but will make such strings malformed.
  21. -- example, if '1.5e15' is the string we'll check '1.5e15.0e0'
  22. -- which ISNUMERIC won't report as numeric.
  23. IF ISNUMERIC(@Value + '.0e0') = 0 RETURN NULL;
  24.  
  25. -- Use numeric(38,0) because
  26. -- a) it's the most number of digits we can parse using built-in types
  27. -- b) we already verified the string contains no decimals
  28. -- Once we have a numeric type, we can do range checks for int type
  29. DECLARE @Result numeric(38,0) = CONVERT(numeric(38,0), @Value);
  30. IF @Result > 9223372036854775807 OR @Result < -9223372036854775808 RETURN NULL;
  31.  
  32. RETURN CONVERT(bigint, @Result);
  33.  
  34. END
Add Comment
Please, Sign In to add comment