SQL Server: erreur de conversion du type de données varchar en numérique

SQL Server 2012 and Later

il suffit d'utiliser Try_Convert au lieu de:

TRY_CONVERT prend la valeur qui lui est transmise et tente de la convertir au type de données spécifié. Si le cast réussit, TRY_CONVERT renvoie la valeur comme le type de données spécifié; si une erreur se produit, null est retourné. Cependant, si vous demandez une conversion qui est explicitement interdite, alors TRY_CONVERT échoue avec une erreur.

en savoir plus à propos de Try_Convert.

SQL Server 2008 and Earlier

la façon traditionnelle de gérer cela est de garder chaque expression avec un énoncé de cas de sorte que n'importe quand il est évalué, il ne créera pas une erreur, même s'il semble logiquement que l'énoncé de cas ne devrait pas être nécessaire. Quelque chose comme ceci:

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

cependant, j'aime utiliser des stratégies comme celle-ci avec SQL Server 2005 et jusqu':

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

ce que cela fait est de changer stratégiquement le Account_Code valeurs NULL à l'intérieur de l' X table quand ils ne sont pas numériques. J'ai d'abord utilisé CROSS APPLY mais Mikael Eriksson si bien souligné, cela a abouti à la même erreur parce que l'analyseur de requête a rencontré exactement le même problème d'optimisation de ma tentative de forcer l'ordre d'expression (predicate pushdown l'a défait). En passant à OUTER APPLY il a changé le sens réel de la le fonctionnement de sorte que X.Account_Code contient NULL valeurs dans la requête externe, ce qui nécessite un ordre d'évaluation approprié.

Vous pouvez être intéressé à lire Erland Sommarskog Microsoft de demande de connexion à propos de cette question d'ordre d'évaluation. En effet, il les appelle ça un bug.

il y a d'autres problèmes ici, mais je ne peux pas les aborder maintenant.

P.S. j'ai eu un brainstorm aujourd'hui. Une alternative à la" voie traditionnelle " que je il est suggéré de faire un SELECT expression avec une référence externe, qui fonctionne aussi dans SQL Server 2000. (J'ai remarqué que depuis que l'apprentissage CROSS/OUTER APPLY j'ai amélioré ma capacité de requête avec des versions plus anciennes de SQL Server,aussi -- car je suis de plus en plus polyvalent avec les capacités de" référence externe " de SELECT,ON et WHERE les clauses!)

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

C'est beaucoup plus courte que la CASE déclaration.

il n'y a aucune garantie que SQL Server ne tentera pas d'effectuer le CONVERTnumeric(20,0)avant il fonctionne le filtre dans le WHERE l'article.

Et, même si elle n', ISNUMERIC n'est pas adéquat, car il reconnaît £ et 1d4 comme étant numérique, ni de ce qui peut être convertie numeric(20,0).(*)

divisez-le en deux requêtes séparées, la première filtrant les résultats et les plaçant dans une table de température ou une variable de table, la seconde qui effectue la conversion. (Les sous-séries et les CTE sont inadéquates pour empêcher l'optimiseur de tenter la conversion avant le filtre)

Pour votre filtre, probablement utiliser account_code not like '%[^0-9]%' au lieu de ISNUMERIC.

(*)ISNUMERIC répond à la question que personne (pour autant que je sache) n'a jamais voulu poser - " cette chaîne peut-elle être convertie en des types de données numériques-Je m'en fous lequel?"- alors, évidemment, ce que la plupart des gens veulent ask est "cette chaîne peut-elle être convertie en x?"où x est un certains cible type de données.

si vous utilisez SQL Server 2012, vous pouvez aussi utiliser le nouveau TRY_PARSE () fonction:

renvoie le résultat d'une expression, traduite aux données demandées tapez, ou null si la Distribution échoue dans SQL Server 2012. Utilisez seulement TRY_PARSE pour convertir des types de chaîne en date / heure et nombre.

je pense que le problème n'est pas dans la sous-requête mais dans la clause WHERE de la requête externe. Lorsque vous utilisez

WHERE account_code between 503100 and 503105

SQL server va essayer de convertir chaque valeur de votre champ Account_code en entier pour le tester dans l'état fourni. Évidemment, il ne le fera pas s'il y aura des caractères non-entiers dans certaines lignes.

merci, essayez plutôt ceci

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

je suis heureux de vous aider