Solución:
Después de probar, descubrí que no era el lugar decimal lo que estaba causando el problema, era la precisión (10)
Esto no funciona: error de desbordamiento aritmético al convertir varchar a tipo de datos numérico.
DECLARE @TestConvert VARCHAR(MAX) = '123456789.12343594'
SELECT CAST(@TestConvert AS DECIMAL(10, 4))
Esto funcionó
DECLARE @TestConvert VARCHAR(MAX) = '123456789.12343594'
SELECT CAST(@TestConvert AS DECIMAL(18, 4))
Mi explicación está en el código. 🙂
DECLARE @TestConvert VARCHAR(MAX) = '123456789.1234567'
BEGIN TRY
SELECT CAST(@TestConvert AS DECIMAL(10, 4))
END TRY
BEGIN CATCH
SELECT 'The reason you get the message "' + ERROR_MESSAGE() + '" is because DECIMAL(10, 4) only allows for 4 numbers after the decimal.'
END CATCH
-- Here's one way to truncate the string to a castable value.
SELECT CAST(LEFT(@TestConvert, (CHARINDEX('.', @TestConvert, 1) + 4)) AS DECIMAL(14, 4))
-- If you noticed, I changed it to DECIMAL(14, 4) instead of DECIMAL(10, 4) That's because this number has 14 digits, as proven below.
-- Read this for a better explanation as to what precision, scale and length mean: http://msdn.microsoft.com/en-us/library/ms190476(v=sql.105).aspx
SELECT LEN(LEFT(@TestConvert, (CHARINDEX('.', @TestConvert, 1) + 4)))
Se me ocurrió la siguiente solución:
SELECT [Str], DecimalParsed = CASE
WHEN ISNUMERIC([Str]) = 1 AND CHARINDEX('.', [Str])=0 AND LEN(REPLACE(REPLACE([Str], '-', ''), '+', '')) < 29 THEN CONVERT(decimal(38,10), [Str])
WHEN ISNUMERIC([Str]) = 1 AND (CHARINDEX('.', [Str])!=0 AND CHARINDEX('.', REPLACE(REPLACE([Str], '-', ''), '+', ''))<=29) THEN
CONVERT(decimal(38,10),
CASE WHEN LEN([Str]) - LEN(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE([Str], '0', ''), '1', ''), '2', ''), '3', ''), '4', ''), '5', ''), '6', ''), '7', ''), '8', ''), '9', '')) <= 38
THEN [Str]
ELSE SUBSTRING([Str], 1, 38 + LEN(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE([Str], '0', ''), '1', ''), '2', ''), '3', ''), '4', ''), '5', ''), '6', ''), '7', ''), '8', ''), '9', ''))) END)
ELSE NULL END
FROM TestStrToDecimal
Sé que parece una exageración y probablemente lo sea, pero funciona para mí (marcó números positivos, negativos, grandes y pequeños de diferente precisión y escala; todo se convierte a decimal(38,10)
o NULL
).
Está codificado para decimal(38,10)
escriba, por lo que si necesita una precisión diferente, cambie las constantes en el código (38, 10, 29).
¿Cómo funciona? El resultado es:
- si la conversión es simple sin desbordamiento o pérdida de precisión (por ejemplo, 123 o 123.456), simplemente lo convierte.
- si el número no es demasiado grande, pero tiene demasiados dígitos después del punto decimal (por ejemplo, 123.1234567890123456789012345678901234567890), entonces recorta los dígitos excedentes al final manteniendo solo 38 primeros dígitos.
- si el número es demasiado grande y no se puede convertir a decimal sin un desbordamiento (por ejemplo, 9876543210987654321098765432109876543210), se devuelve NULL
cada caso es una declaración WHEN separada en el código anterior.
A continuación, se muestran algunos ejemplos de conversión: