risposta-alla-domanda-sullo-sviluppo-web-bd.com

SQL Server: errore durante la conversione del tipo di dati varchar in numerico

Ho un tavolo:

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

Dove Account_Code è un varchar.

Quando creo una query di seguito:

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

Funziona bene restituendo tutti i record che hanno un valore numerico valido in account_code colonna.

Ma quando provo ad aggiungere un'altra selezione, nidificata a sql precedente:

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 query restituirà un errore

Errore durante la conversione del tipo di dati varchar in numerico.

Cosa sta succedendo lì?

Ho già convertito in numerico se account_code valido, ma sembra che la query stia ancora tentando di elaborare un record non valido.

Ho bisogno di usare la clausola BETWEEN nella mia query.

21
user1947840

SQL Server 2012 e versioni successive

Usa Try_Convert anziché:

TRY_CONVERT accetta il valore passato ad esso e tenta di convertirlo nel tipo di dati specificato. Se il cast ha esito positivo, TRY_CONVERT restituisce il valore come tipo di dati specificato; se si verifica un errore, viene restituito null. Tuttavia, se richiedi una conversione esplicitamente non consentita, TRY_CONVERT non riesce con un errore.

lteriori informazioni su Try_Convert .

SQL Server 2008 e precedenti

Il modo tradizionale di gestirlo è tutelare ogni espressione con un'istruzione case in modo che, indipendentemente dalla valutazione, non creerà un errore, anche se logicamente sembra che l'istruzione CASE non dovrebbe essere necessaria. Qualcosa come questo:

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

Tuttavia, mi piace usare strategie come questa con SQL Server 2005 e versioni successive:

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

Quello che fa è cambiare strategicamente il Account_Code valori a NULL all'interno della tabella X quando non sono numerici. Inizialmente ho usato CROSS APPLY ma come Mikael Eriksson così sottolineato in modo appropriato, questo ha provocato lo stesso errore perché il parser di query ha riscontrato lo stesso identico problema di ottimizzare il mio tentativo di forzare l'ordine delle espressioni (il pushdown del predicato lo ha sconfitto) . Passando a OUTER APPLY ha cambiato il significato effettivo dell'operazione in modo che X.Account_Code could contiene NULL valori all'interno della query esterna, richiedendo quindi un corretto ordine di valutazione.

Potresti essere interessato a leggere richiesta Microsoft Connect di Erland Sommarskog su questo problema relativo all'ordine di valutazione. In realtà lo chiama un bug.

Ci sono altri problemi qui, ma non posso risolverli ora.

Post scriptum Ho avuto un brainstorming oggi. Un'alternativa al "modo tradizionale" che ho suggerito è un'espressione SELECT con un riferimento esterno, che funziona anche in SQL Server 2000. (Ho notato che dall'apprendimento CROSS/OUTER APPLY Ho migliorato la mia capacità di query anche con le versioni precedenti di SQL Server - mentre sto diventando più versatile con le funzionalità di "riferimento esterno" di SELECT, ON e WHERE clausole!)

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

È molto più breve dell'istruzione CASE.

29
ErikE

Non vi è alcuna garanzia che SQL Server non tenterà di eseguire da CONVERT a numeric(20,0)prima esegue il filtro nella clausola WHERE.

E, anche se lo facesse, ISNUMERIC non è adeguato, poiché riconosce £ E 1d4 Come numerici, nessuno dei quali può essere convertito in numeric(20,0). (*)

Dividilo in due query separate, la prima delle quali filtra i risultati e li inserisce in una tabella temporanea o variabile di tabella, la seconda delle quali esegue la conversione. (Le subquery e le CTE sono inadeguate per impedire all'ottimizzatore di tentare la conversione prima del filtro)

Per il tuo filtro, probabilmente usa account_code not like '%[^0-9]%' Invece di ISNUMERIC.


(*) ISNUMERIC risponde alla domanda che nessuno (per quanto ne sappia) ha mai voluto porre - "questa stringa può essere convertita in any dei tipi di dati numerici - Non mi importa quale? " - quando ovviamente, ciò che molte persone vogliono chiedere è "questa stringa può essere convertita in x?" dove x è un tipo di dati specifico target.

Se si esegue SQL Server 2012 o versioni successive, è anche possibile utilizzare la nuova funzione TRY_PARSE () :

Restituisce il risultato di un'espressione, tradotto nel tipo di dati richiesto o null se il cast non riesce in SQL Server. Utilizzare TRY_PARSE solo per la conversione da stringa a data/ora e tipi di numero.

Oppure TRY_CONVERT / TRY_CAST :

Restituisce un cast di valori nel tipo di dati specificato se il cast ha esito positivo; in caso contrario, restituisce null.

7
mprost

grazie, prova questo invece

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

Sono felice di aiutarti

1
Majed jemmieh

Penso che il problema non sia nella query secondaria ma nella clausola WHERE della query esterna. Quando si usa

WHERE account_code between 503100 and 503105

Il server SQL proverà a convertire tutti i valori nel campo Account_code in numero intero per testarlo nelle condizioni fornite. Ovviamente non ci riuscirà se ci saranno caratteri non interi in alcune righe.

1
Blindfold