TRY_CAST Function for SQL Server 2008

dbo.TRY_CAST.sql

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

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!