-
-
Save jotapardo/800881eba8c5072eb8d99ce6eb74c8bb to your computer and use it in GitHub Desktop.
DECLARE @strSQL NVARCHAR(1000) | |
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[TRY_CAST]')) | |
BEGIN | |
SET @strSQL = 'CREATE FUNCTION [dbo].[TRY_CAST] () RETURNS INT AS BEGIN RETURN 0 END' | |
EXEC sys.sp_executesql @strSQL | |
END | |
SET ANSI_NULLS ON | |
GO | |
SET QUOTED_IDENTIFIER ON | |
GO | |
/* | |
------------------------------------------------------------------------------------------------------------------------ | |
Description: | |
Syntax | |
--------------- | |
dbo.TRY_CAST(Expression, Data_Type, ReturnValueIfErrorCast) | |
+---------------------------+-----------------------+ | |
| Expression | VARCHAR(8000) | | |
+---------------------------+-----------------------+ | |
| Data_Type | VARCHAR(8000) | | |
+---------------------------+-----------------------+ | |
| ReturnValueIfErrorCast | SQL_VARIANT = NULL | | |
+---------------------------+-----------------------+ | |
Arguments | |
--------------- | |
expression | |
The value to be cast. Any valid expression. | |
Data_Type | |
The data type into which to cast expression. | |
ReturnValueIfErrorCast | |
Value returned if cast fails or is not supported. Required. Set the DEFAULT value by default. | |
Return Type | |
---------------- | |
Returns value cast to SQL_VARIANT type if the cast succeeds; otherwise, returns null if the parameter @pReturnValueIfErrorCast is set to DEFAULT, | |
or that the user indicates. | |
Remarks | |
---------------- | |
dbo.TRY_CAST function simulates the TRY_CAST function reserved of SQL SERVER 2012 for using in SQL SERVER 2008. | |
dbo.TRY_CAST function takes the value passed to it and tries to convert it to the specified Data_Type. | |
If the cast succeeds, dbo.TRY_CAST returns the value as SQL_VARIANT type; if the cast doesn´t succees, null is returned if the parameter @pReturnValueIfErrorCast is set to DEFAULT. | |
If the Data_Type is unsupported will return @pReturnValueIfErrorCast. | |
dbo.TRY_CAST function requires user make an explicit CAST or CONVERT in ANY statements. | |
This version of dbo.TRY_CAST only supports CAST for INT, DATE, NUMERIC and BIT types. | |
Examples | |
==================================================================================================== | |
--A. Test TRY_CAST function returns null | |
SELECT | |
CASE WHEN dbo.TRY_CAST('6666666166666212', 'INT', DEFAULT) IS NULL | |
THEN 'Cast failed' | |
ELSE 'Cast succeeded' | |
END AS Result; | |
GO | |
--B. Error Cast With User Value | |
SELECT | |
dbo.TRY_CAST('2147483648', 'INT', DEFAULT) AS [Error Cast With DEFAULT], | |
dbo.TRY_CAST('2147483648', 'INT', -1) AS [Error Cast With User Value], | |
dbo.TRY_CAST('2147483648', 'INT', NULL) AS [Error Cast With User NULL Value]; | |
GO | |
--C. Additional CAST or CONVERT required in any assignment statement | |
DECLARE @IntegerVariable AS INT | |
SET @IntegerVariable = CAST(dbo.TRY_CAST(123, 'INT', DEFAULT) AS INT) | |
SELECT @IntegerVariable | |
GO | |
IF OBJECT_ID('tempdb..#temp') IS NOT NULL | |
DROP TABLE #temp | |
CREATE TABLE #temp ( | |
Id INT IDENTITY | |
, FieldNumeric NUMERIC(3, 1) | |
) | |
INSERT INTO dbo.#temp (FieldNumeric) | |
SELECT CAST(dbo.TRY_CAST(12.3, 'NUMERIC(3,1)', 0) AS NUMERIC(3, 1));--Need explicit CAST on INSERT statements | |
SELECT * | |
FROM #temp | |
DROP TABLE #temp | |
GO | |
--D. Supports CAST for INT, DATE, NUMERIC and BIT types. | |
SELECT dbo.TRY_CAST(2147483648, 'INT', 0) AS [Cast failed] | |
, dbo.TRY_CAST(2147483647, 'INT', 0) AS [Cast succeeded] | |
, SQL_VARIANT_PROPERTY(dbo.TRY_CAST(212, 'INT', 0), 'BaseType') AS [BaseType]; | |
SELECT dbo.TRY_CAST('AAAA0101', 'DATE', DEFAULT) AS [Cast failed] | |
, dbo.TRY_CAST('20160101', 'DATE', DEFAULT) AS [Cast succeeded] | |
, SQL_VARIANT_PROPERTY(dbo.TRY_CAST('2016-01-01', 'DATE', DEFAULT), 'BaseType') AS [BaseType]; | |
SELECT dbo.TRY_CAST(1.23, 'NUMERIC(3,1)', DEFAULT) AS [Cast failed] | |
, dbo.TRY_CAST(12.3, 'NUMERIC(3,1)', DEFAULT) AS [Cast succeeded] | |
, SQL_VARIANT_PROPERTY(dbo.TRY_CAST(12.3, 'NUMERIC(3,1)', DEFAULT), 'BaseType') AS [BaseType]; | |
SELECT dbo.TRY_CAST('A', 'BIT', DEFAULT) AS [Cast failed] | |
, dbo.TRY_CAST(1, 'BIT', DEFAULT) AS [Cast succeeded] | |
, SQL_VARIANT_PROPERTY(dbo.TRY_CAST('123', 'BIT', DEFAULT), 'BaseType') AS [BaseType]; | |
GO | |
--E. B. TRY_CAST return NULL on unsupported data_types | |
SELECT dbo.TRY_CAST(4, 'xml', DEFAULT) AS [unsupported]; | |
GO | |
==================================================================================================== | |
------------------------------------------------------------------------------------------------------------------------ | |
Responsible: Javier Pardo | |
Date: diciembre 29/2016 | |
WB tests: Javier Pardo | |
------------------------------------------------------------------------------------------------------------------------ | |
Update by: Javier Eduardo Pardo Moreno | |
Date: febrero 16/2017 | |
Id update: JEPM20170216 | |
Description: Fix ISNUMERIC function makes it unreliable. SELECT dbo.TRY_CAST('+', 'INT', 0) will yield Msg 8114, | |
Level 16, State 5, Line 16 Error converting data type varchar to float. | |
ISNUMERIC() function treats few more characters as numeric, like: – (minus), + (plus), $ (dollar), \ (back slash), (.)dot and (,)comma | |
Collaborator aperiooculus (http://stackoverflow.com/users/3083382/aperiooculus ) | |
Fix dbo.TRY_CAST('2013/09/20', 'datetime', DEFAULT) for supporting DATETIME format | |
WB tests: Javier Pardo | |
------------------------------------------------------------------------------------------------------------------------ | |
*/ | |
ALTER FUNCTION dbo.TRY_CAST | |
( | |
@pExpression AS VARCHAR(8000), | |
@pData_Type AS VARCHAR(8000), | |
@pReturnValueIfErrorCast AS SQL_VARIANT = NULL | |
) | |
RETURNS SQL_VARIANT | |
AS | |
BEGIN | |
-------------------------------------------------------------------------------- | |
-- INT | |
-------------------------------------------------------------------------------- | |
IF @pData_Type = 'INT' | |
BEGIN | |
IF ISNUMERIC(@pExpression) = 1 AND @pExpression NOT IN ('-','+','$','.',',','\') --JEPM20170216 | |
BEGIN | |
DECLARE @pExpressionINT AS FLOAT = CAST(@pExpression AS FLOAT) | |
IF @pExpressionINT BETWEEN - 2147483648.0 AND 2147483647.0 | |
BEGIN | |
RETURN CAST(@pExpressionINT as INT) | |
END | |
ELSE | |
BEGIN | |
RETURN @pReturnValueIfErrorCast | |
END --FIN IF @pExpressionINT BETWEEN - 2147483648.0 AND 2147483647.0 | |
END | |
ELSE | |
BEGIN | |
RETURN @pReturnValueIfErrorCast | |
END -- FIN IF ISNUMERIC(@pExpression) = 1 | |
END -- FIN IF @pData_Type = 'INT' | |
-------------------------------------------------------------------------------- | |
-- DATE | |
-------------------------------------------------------------------------------- | |
IF @pData_Type IN ('DATE','DATETIME') | |
BEGIN | |
IF ISDATE(@pExpression) = 1 | |
BEGIN | |
DECLARE @pExpressionDATE AS DATETIME = cast(@pExpression AS DATETIME) | |
IF @pData_Type = 'DATE' | |
BEGIN | |
RETURN cast(@pExpressionDATE as DATE) | |
END | |
IF @pData_Type = 'DATETIME' | |
BEGIN | |
RETURN cast(@pExpressionDATE as DATETIME) | |
END | |
END | |
ELSE | |
BEGIN | |
DECLARE @pExpressionDATEReplaced AS VARCHAR(50) = REPLACE(REPLACE(REPLACE(@pExpression,'\',''),'/',''),'-','') | |
IF ISDATE(@pExpressionDATEReplaced) = 1 | |
BEGIN | |
IF @pData_Type = 'DATE' | |
BEGIN | |
RETURN cast(@pExpressionDATEReplaced as DATE) | |
END | |
IF @pData_Type = 'DATETIME' | |
BEGIN | |
RETURN cast(@pExpressionDATEReplaced as DATETIME) | |
END | |
END | |
ELSE | |
BEGIN | |
RETURN @pReturnValueIfErrorCast | |
END | |
END --FIN IF ISDATE(@pExpression) = 1 | |
END --FIN IF @pData_Type = 'DATE' | |
-------------------------------------------------------------------------------- | |
-- NUMERIC | |
-------------------------------------------------------------------------------- | |
IF @pData_Type LIKE 'NUMERIC%' | |
BEGIN | |
IF ISNUMERIC(@pExpression) = 1 | |
BEGIN | |
DECLARE @TotalDigitsOfType AS INT = SUBSTRING(@pData_Type,CHARINDEX('(',@pData_Type)+1, CHARINDEX(',',@pData_Type) - CHARINDEX('(',@pData_Type) - 1) | |
, @TotalDecimalsOfType AS INT = SUBSTRING(@pData_Type,CHARINDEX(',',@pData_Type)+1, CHARINDEX(')',@pData_Type) - CHARINDEX(',',@pData_Type) - 1) | |
, @TotalDigitsOfValue AS INT | |
, @TotalDecimalsOfValue AS INT | |
, @TotalWholeDigitsOfType AS INT | |
, @TotalWholeDigitsOfValue AS INT | |
SET @pExpression = REPLACE(@pExpression, ',','.') | |
SET @TotalDigitsOfValue = LEN(REPLACE(@pExpression, '.','')) | |
SET @TotalDecimalsOfValue = CASE Charindex('.', @pExpression) | |
WHEN 0 | |
THEN 0 | |
ELSE Len(Cast(Cast(Reverse(CONVERT(VARCHAR(50), @pExpression, 128)) AS FLOAT) AS BIGINT)) | |
END | |
SET @TotalWholeDigitsOfType = @TotalDigitsOfType - @TotalDecimalsOfType | |
SET @TotalWholeDigitsOfValue = @TotalDigitsOfValue - @TotalDecimalsOfValue | |
-- The total digits can not be greater than the p part of NUMERIC (p, s) | |
-- The total of decimals can not be greater than the part s of NUMERIC (p, s) | |
-- The total digits of the whole part can not be greater than the subtraction between p and s | |
IF (@TotalDigitsOfValue <= @TotalDigitsOfType) AND (@TotalDecimalsOfValue <= @TotalDecimalsOfType) AND (@TotalWholeDigitsOfValue <= @TotalWholeDigitsOfType) | |
BEGIN | |
DECLARE @pExpressionNUMERIC AS FLOAT | |
SET @pExpressionNUMERIC = CAST (ROUND(@pExpression, @TotalDecimalsOfValue) AS FLOAT) | |
RETURN @pExpressionNUMERIC --Returns type FLOAT | |
END | |
else | |
BEGIN | |
RETURN @pReturnValueIfErrorCast | |
END-- FIN IF (@TotalDigitisOfValue <= @TotalDigits) AND (@TotalDecimalsOfValue <= @TotalDecimals) | |
END | |
ELSE | |
BEGIN | |
RETURN @pReturnValueIfErrorCast | |
END --FIN IF ISNUMERIC(@pExpression) = 1 | |
END --IF @pData_Type LIKE 'NUMERIC%' | |
-------------------------------------------------------------------------------- | |
-- BIT | |
-------------------------------------------------------------------------------- | |
IF @pData_Type LIKE 'BIT' | |
BEGIN | |
IF ISNUMERIC(@pExpression) = 1 | |
BEGIN | |
RETURN CAST(@pExpression AS BIT) | |
END | |
ELSE | |
BEGIN | |
RETURN @pReturnValueIfErrorCast | |
END --FIN IF ISNUMERIC(@pExpression) = 1 | |
END --IF @pData_Type LIKE 'BIT' | |
-------------------------------------------------------------------------------- | |
-- FLOAT | |
-------------------------------------------------------------------------------- | |
IF @pData_Type LIKE 'FLOAT' | |
BEGIN | |
IF ISNUMERIC(REPLACE(REPLACE(@pExpression, CHAR(13), ''), CHAR(10), '')) = 1 | |
BEGIN | |
RETURN CAST(@pExpression AS FLOAT) | |
END | |
ELSE | |
BEGIN | |
IF REPLACE(@pExpression, CHAR(13), '') = '' --Only white spaces are replaced, not new lines | |
BEGIN | |
RETURN 0 | |
END | |
ELSE | |
BEGIN | |
RETURN @pReturnValueIfErrorCast | |
END --IF REPLACE(@pExpression, CHAR(13), '') = '' | |
END --FIN IF ISNUMERIC(@pExpression) = 1 | |
END --IF @pData_Type LIKE 'FLOAT' | |
-------------------------------------------------------------------------------- | |
-- Any other unsupported data type will return NULL or the value assigned by the user to @pReturnValueIfErrorCast | |
-------------------------------------------------------------------------------- | |
RETURN @pReturnValueIfErrorCast | |
END |
FLOAT cast is added
Fix ISNUMERIC function makes it unreliable. SELECT dbo.TRY_CAST('+', 'INT', 0)
will yield Msg 8114,
Level 16, State 5, Line 16 Error converting data type varchar to float.
ISNUMERIC() function treats few more characters as numeric, like: – (minus), + (plus), $ (dollar), \ (back slash), (.)dot and (,)comma
Collaborator aperiooculus (http://stackoverflow.com/users/3083382/aperiooculus )
Fix dbo.TRY_CAST('2013/09/20', 'datetime', DEFAULT) for supporting DATETIME format
Is this under the FaiB license (Free as in Beer). I would like to reuse it w/credits and I will gladly buy you a beer if we ever cross paths!
Is this under the FaiB license (Free as in Beer). I would like to reuse it w/credits and I will gladly buy you a beer if we ever cross paths!
Of course @neurocis! I very much appreciate your offer. It is precisely to make a better world with SQL. So yeah... is FaiB License!
Version 1.0.0