try_cast - Try_Convert para SQL Server 2008 R2

try convert sql server 2008

Question

Estoy usando SQL Server 2008 R2 y tengo una columna VARCHAR que quiero convertir a DECIMAL(28,10) usando CONVERT . Pero muchas de esas filas están mal formateadas, por lo que no es posible analizarlas en un número. En ese caso, solo quiero omitir esos al establecer el resultado en 0 o NULO.

Sé que hay una nueva declaración en SQL Server 2012 ( TRY_CONVERT() ) que sería útil.

¿Es esto posible en 2008 o debo esperar hasta que actualicemos a la próxima versión de SQL 2012?

EDITAR

Desafortunadamente ISNUMERIC() no es confiable en este caso. Lo intenté

ISNUMERIC(myCol) = 1

Esto devuelve verdadero para las filas que CONVERT no puede convertir a DECIMAL .

Answer 1

Al usar XML en SQL Server puede tratar de convertir a un tipo de datos y recibir valores nulos donde falla el lanzamiento.

declare @T table
(
Col varchar(50)
)
insert into @T values
(''1''),
(''1.1''),
(''1,1''),
(''1a'')
select cast('''' as xml).value(''sql:column("Col") cast as xs:decimal ?'',
''decimal(28,10)'') as Col
from @T

Resultado:

Col
-------------
1.0000000000
1.1000000000
NULL
NULL

Answer 2

Como se trata de un cambio permanente, lo haría como un proceso de dos pasos: primero, elimine el texto no válido y luego convierta la columna.

Para eliminar el texto no válido, haría algo como:

UPDATE [Table]
SET [Column] = NULL
WHERE [Column] LIKE ''%[^0-9.]%'' or
LEN([Column]) - LEN(REPLACE([Column],''.'','''')) > 1 or
LEN([Column]) > 28

Una vez hecho esto, todo lo que queda debe ser convertible simplemente cambiando la definición de columna

ALTER TABLE [Table] ALTER COLUMN [Column] decimal(28,10)

Answer 3

Escribí una función escalar útil para simular la función TRY_CAST de SQL Server 2012 en SQL Server 2008.

dbo.TRY_CAST(Expression, Data_Type, ReturnValueIfErrorCast)

Las dos principales diferencias con TRY_CAST Function para SQL Server 2012 son que debe pasar 3 parámetros y además debe realizar un CONVERT o CAST explícito en el campo. Sin embargo, sigue siendo muy útil porque le permite devolver un valor predeterminado si CAST no se realiza correctamente.

CÓDIGO DE FUNCIÓN:

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://.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

Por ahora, solo admite los tipos de datos INT, DATE, DATETIME, NUMERIC, BIT y FLOAT . Puede encontrar la última versión de este código en el siguiente enlace a continuación y nos ayudamos mutuamente para mejorarlo. Función TRY_CAST para SQL Server 2008 https://gist.github.com/jotapardo/800881eba8c5072eb8d99ce6eb74c8bb

Answer 4

Finalmente descubrí cómo hacerlo con la ayuda de SO y Google.

La declaración de actualización:

UPDATE PriceTerm
SET PercentAddition = CONVERT(decimal(28,10), RTRIM(LTRIM(REPLACE(REPLACE(REPLACE(AdditionalDescription,''%'',''''), '','',''.''), ''&'', ''''))))
WHERE AdditionalDescription LIKE ''%[%]%'' AND
dbo.isreallynumeric(RTRIM(LTRIM(REPLACE(REPLACE(REPLACE(AdditionalDescription,''%'',''''), '','',''.''), ''&'', '''')))) = 1 AND
PercentAddition = 0

Primero busco% char como la mayoría de las veces que se utiliza como marcador para el valor porcentual. Pero también hay otros usos al azar. Resultó que ISNUMERIC no era confiable en mi caso.

Lo que realmente hace la diferencia es que la llamada al procedimiento almacenado es realinemónica desde here .

Asi que

CREATE FUNCTION dbo.isReallyNumeric
(
@num VARCHAR(64)
)
RETURNS BIT
BEGIN
IF LEFT(@num, 1) = ''-''
SET @num = SUBSTRING(@num, 2, LEN(@num))
DECLARE @pos TINYINT
SET @pos = 1 + LEN(@num) - CHARINDEX(''.'', REVERSE(@num))
RETURN CASE
WHEN PATINDEX(''%[^0-9.-]%'', @num) = 0
AND @num NOT IN (''.'', ''-'', ''+'', ''^'')
AND LEN(@num)>0
AND @num NOT LIKE ''%-%''
AND
(
((@pos = LEN(@num)+1)
OR @pos = CHARINDEX(''.'', @num))
)
THEN
1
ELSE
0
END
END
GO

Answer 5

Prefiero soluciones simples e hice un SP como el siguiente.

CREATE PROC TRY_CAST
(
@valueToBeParsed VARCHAR(64), @parsedValue INT OUTPUT
)
AS
BEGIN
BEGIN TRY
SELECT @parsedValue = cast(@valueToBeParsed as int)
END TRY
BEGIN CATCH
SET @parsedValue = null
END CATCH
END
GO

y lo usé como siguiendo

DECLARE @val int
EXEC TRY_CAST ''1w'', @val out
select @val

Answer 6

Puede escribir su propio analizador personalizado en C # y usar SQLCLR, usando, por ejemplo, Decimal.Parse() . No intente utilizar ISNUMERIC , es notoriamente incorrect (devuelve TRUE para las cadenas que no pueden CAST).

Answer 7

SQL SERVER 2008 R2 tiene una función TRY_CAST pero si configura su DB con compatibilidad con 2005, encontró que SQL SERVER no lo reconoce.

Utilicé la función que crea pero renombre TRY_CAST2 porque SS en este caso reconoció la función.