SQL Server ISNUMERIC () Clarification

J'ai une table X avec une list de accountNo. Ce champ (accoutNo) est un nvarchar (8). Maintenant la question ici est parfois nous obtenons des caractères dans ce domaine aussi et je veux le convertir en bigint.

Désolé je ne peux pas mettre ceci dans un format de table ici.

Je suis capable de vérifier si un countNo est une valeur numérique ou non:

select x.accountNo from x where ISNUMERIC(x.accountNo)=1

mais quand j'essaye de convertir seulement les valeurs si un countNo est numérique, je ne peux toujours pas, je suis confus:

 select x.accountNo, convert(bigint,x.accountNo) from x where ISNUMERIC(x.accountNo)=1

L'erreur spécifique que je reçois:

Msg 8114, niveau 16, état 5, ligne 1 Erreur lors de la conversion du type de données nvarchar en bigint.

Exemple de données

 accountNo A0001001 A0001002 A0001003 /0005856 !0005046 ~0005872 A.005698 A/005623 A./00578 ./214536

Vous devriez utiliser CAST() ou TRY_CAST() place:

 declare @test nvarchar(8) = '12345678' select cast(@test as bigint) -- errors on failure select try_cast(@test as bigint) -- returns null on failure

Aussi, important de souligner le ISNUMERIC() n'est pas parfait. De la docs :

ISNUMERIC renvoie 1 pour certains caractères qui ne sont pas des nombres, tels que plus (+), less (-) et des symboles monétaires valides tels que le symbole dollar ($). Pour une list complète des symboles monétaires, voir money et smallmoney (Transact-SQL).

Pour cette raison, je ne pense pas que la vérification logique ait de la valeur ici. Il est TRY_CAST() utiliser TRY_CAST() sur toutes les valeurs, indépendamment de la présence de caractères et de gérer la réponse nulle d'une manière prévisible.

C'est un problème que je rencontre fréquemment. La raison pour laquelle vous obtenez l'erreur est liée à l'ordre dans lequel le moteur de search parsing la requête. Essentiellement, il essaie de faire la conversion avant le filter.

Comme mentionné dans la réponse de pim, vous pouvez résoudre cela en utilisant un try_cast, ou si vous utilisez une version antérieure du server SQL, vous pouvez l'entourer d'une instruction case:

 select CASE WHEN ISNUMERIC(@test) = 1 THEN CONVERT(bigint, @test) ELSE 0 END

J'ai essayé ci-dessous la requête et n'ai pas eu d'erreur. J'ai testé sur SQL 2012 et 2014.

 with c as ( select 'A0001001' as acc union all select 'A0001002' as acc union all select 'A0001003' as acc union all select '/0005856' as acc union all select '!0005046' as acc union all select '~0005872' as acc union all select 'A.005698' as acc union all select 'A/005623' as acc union all select 'A./00578' as acc union all select './214536' as acc ) select isnumeric(acc), convert(bigint, acc) from c where isnumeric(acc) = 1

S'il vous plaît essayez ceci sur votre database pour voir si vous obtenez une erreur ou non.

Cela devrait être un commentaire, mais il est trop long pour append un commentaire. J'espère que cela vous aidera à comprendre votre problème.