solucion - SQL Server: error al convertir el tipo de datos varchar a numérico

error converting data type varchar to numeric solucion

Question

Tengo una mesa

Account_Code | Desc
503100       | account xxx
503103       | account xxx
503104       | account xxx
503102A      | account xxx
503110B      | account xxx

Donde Account_Code es un varchar .

Cuando creo una consulta a continuación:

Select 
  cast(account_code as numeric(20,0)) as account_code,
  descr 
from account 
where isnumeric(account_code) = 1

Se ejecuta bien devolviendo todos los registros que tienen un valor numérico válido en la columna account_code .

Pero cuando intento agregar otra selección, anidada a sql anterior:

select account_code,descr 
from 
(
  Select cast(account_code as numeric(20, 0)) as account_code,descr 
  from account 
  where isnumeric(account_code) = 1
) a 
WHERE account_code between 503100 and 503105

la consulta devolverá un error

Error al convertir el tipo de datos varchar a numérico.

¿Lo que está sucediendo allí?

Ya he convertido a numérico si account_code válido, pero parece que la consulta todavía está tratando de procesar un registro no válido.

Necesito usar la cláusula BETWEEN en mi consulta.

Answer 1

Creo que el problema no está en la subconsulta sino en la cláusula WHERE de la consulta externa. Cuando usas

WHERE account_code between 503100 and 503105

El servidor SQL intentará convertir cada valor en su campo de código de cuenta en un entero para probarlo en la condición proporcionada. Obviamente, no podrá hacerlo si habrá caracteres no enteros en algunas filas.

Answer 2

No hay garantía de que SQL Server no intente realizar la CONVERT numeric(20,0) antes de ejecutar el filtro en la cláusula WHERE .

E incluso si lo hizo, ISNUMERIC no es adecuado, ya que reconoce que £ y 1d4 son numéricos, ninguno de los cuales se puede convertir a numeric(20,0) . (*)

Divídalo en dos consultas separadas, la primera de las cuales filtra los resultados y los coloca en una tabla temporal o variable de tabla, la segunda de las cuales realiza la conversión. (Las subconsultas y los CTE son inadecuados para evitar que el optimizador intente la conversión antes del filtro)

Para su filtro, probablemente use account_code not like ''%[^0-9]%'' lugar de ISNUMERIC .

(*) ISNUMERIC responde a la pregunta que nadie (de lo que yo sepa) ha querido preguntar: "¿se puede convertir esta cadena a cualquiera de los tipos de datos numéricos? ¿No me importa cuál?" - cuando obviamente, lo que la mayoría de la gente quiere preguntar es "¿se puede convertir esta cadena a x?" donde x es un tipo de datos objetivo específico .

Answer 3

Si está ejecutando SQL Server 2012, también puede usar la nueva función TRY_PARSE() :

Devuelve el resultado de una expresión, traducida al tipo de datos solicitado, o nula si la conversión falla en SQL Server 2012. Use TRY_PARSE solo para convertir de cadena a fecha / hora y tipos de números.

Answer 4

gracias, prueba esto en su lugar

Select 
  STR(account_code) as account_code_Numeric,
  descr 
from account 
where  STR(account_code) = 1

Estoy feliz de ayudarte

Answer 5

SQL Server 2012 y posteriores

Solo usa Try_Convert en Try_Convert lugar:

TRY_CONVERT toma el valor que se le pasa e intenta convertirlo en el tipo de datos especificado. Si la conversión tiene éxito, TRY_CONVERT devuelve el valor como el tipo de datos especificado; Si se produce un error, se devuelve un valor nulo. Sin embargo, si solicita una conversión que no está permitida explícitamente, TRY_CONVERT falla con un error.

Lea más sobre Try_Convert .

SQL Server 2008 y versiones anteriores

La forma tradicional de manejar esto es protegiendo cada expresión con una declaración de caso para que no importa cuándo se evalúe, no creará un error, incluso si lógicamente parece que la declaración CASE no debería ser necesaria. Algo como esto:

SELECT
   Account_Code =
      Convert(
         bigint, -- only gives up to 18 digits, so use decimal(20, 0) if you must
         CASE
         WHEN X.Account_Code LIKE ''%[^0-9]%'' THEN NULL
         ELSE X.Account_Code
         END
      ),
   A.Descr
FROM dbo.Account A
WHERE
   Convert(
      bigint,
      CASE
      WHEN X.Account_Code LIKE ''%[^0-9]%'' THEN NULL
      ELSE X.Account_Code
      END
   ) BETWEEN 503100 AND 503205

Sin embargo, me gusta usar estrategias como esta con SQL Server 2005 y más:

SELECT
   Account_Code = Convert(bigint, X.Account_Code),
   A.Descr
FROM
   dbo.Account A
   OUTER APPLY (
      SELECT A.Account_Code WHERE A.Account_Code NOT LIKE ''%[^0-9]%''
   ) X
WHERE
   Convert(bigint, X.Account_Code) BETWEEN 503100 AND 503205

Lo que esto hace es cambiar estratégicamente los valores de Account_Code a NULL dentro de la tabla X cuando no son numéricos. Inicialmente utilicé CROSS APPLY pero como Mikael Eriksson lo señaló tan acertadamente, esto resultó en el mismo error porque el analizador de consultas encontró exactamente el mismo problema de optimizar mi intento de forzar el orden de expresión (predicate pushdown lo venció). Al cambiar a OUTER APPLY , cambió el significado real de la operación, de modo que X.Account_Code podría contener valores NULL dentro de la consulta externa, por lo que se requiere un orden de evaluación adecuado.

Quizás le interese leer la solicitud de Microsoft Connect de Erland Sommarskog sobre este problema de orden de evaluación. Él, de hecho, lo llama un error.

Hay problemas adicionales aquí, pero no puedo resolverlos ahora.

PD tuve una tormenta de ideas hoy. Una alternativa a la "forma tradicional" que sugerí es una expresión SELECT con una referencia externa, que también funciona en SQL Server 2000. (He notado que desde que aprendí CROSS/OUTER APPLY mejoré mi capacidad de consulta con SQL más antiguo Versiones de servidor, también, ya que me estoy volviendo más versátil con las capacidades de "referencia externa" de las cláusulas SELECT , ON y WHERE !)

SELECT
   Account_Code =
      Convert(
         bigint,
         (SELECT A.AccountCode WHERE A.Account_Code NOT LIKE ''%[^0-9]%'')
      ),
   A.Descr
FROM dbo.Account A
WHERE
   Convert(
      bigint,
      (SELECT A.AccountCode WHERE A.Account_Code NOT LIKE ''%[^0-9]%'')
   ) BETWEEN 503100 AND 503205

Es mucho más corto que la declaración CASE .