SQL Server: error omzetten gegevenstype varchar numerieke

Ik heb een lijst:

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

Waar Account_Codeis een varchar.

Als ik een vraag hieronder:

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

Het loopt goed door terug te keren alle record dat een geldige numerieke waarde in Account_Codekolom te hebben.

Maar wanneer ik probeer om een ​​andere te selecteren, genest voorafgaande sql toe te voegen:

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

de query retourneert een fout

Error omzetten datatype varchar numerieke.

Wat gebeurt er?

Ik heb al omgezet in numerieke als Account_Codegeldig is, maar het lijkt erop dat de vraag is nog steeds bezig om een ​​niet geldige registratie te verwerken.

Ik gebruik moeten maken BETWEENclausule in mijn vraag.


Antwoord 1, Autoriteit 100%

SQL Server 2012 en later

Just gebruik Try_Convert

Try_Convert is de waarde die eraan is doorgegeven en probeert het naar de opgegeven data_type te converteren. Als de cast slaagt, retourneert Try_Converteer de waarde als de opgegeven data_type; Als er een fout optreedt, wordt null geretourneerd. Als u echter een conversie vraagt ​​die expliciet niet is toegestaan, mislukt Try_Convert met een fout.

Lees meer over Try_Convert .

SQL Server 2008 en eerder

De traditionele manier van afhandelen dit is door elke uitdrukking te bewaken met een zaakverklaring, zodat het ongeacht wanneer het wordt geëvalueerd, het geen fout zal maken, zelfs als deze logischerwijs lijkt dat de uitspraak niet nodig zou zijn. Zoiets als volgt:

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

Ik hou van strategieën zoals dit met SQL Server 2005 en UP:

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

Wat dit doet is de Account_Code-waarden strategisch naar NULLin de x-tabel veranderen als ze niet numeriek zijn. Ik gebruikte aanvankelijk CROSS APPLYmaar zoals Mikael Erikssonzo treffend aangaf, resulteerde dit in dezelfde fout omdat de query-parser exact hetzelfde probleem tegenkwam bij het optimaliseren van mijn poging om de uitdrukkingsvolgorde te forceren (predicaat pushdown versloeg het). Door over te schakelen naar OUTER APPLYveranderde het de werkelijke betekenis van de bewerking zodat X.Account_CodekonNULL-waarden bevatten binnen de buitenste query, waardoor de juiste evaluatievolgorde vereist is.

Misschien ben je geïnteresseerd in het lezen van Erland Sommarskog’s Microsoft Connect-verzoekover dit probleem met de evaluatieorder. Hij noemt het zelfs een bug.

Er zijn hier nog meer problemen, maar ik kan ze nu niet oplossen.

P.S. Ik had vandaag een brainstorm. Een alternatief voor de “traditionele manier” die ik voorstelde, is een SELECT-expressie met een buitenste referentie, die ook werkt in SQL Server 2000. (Ik heb gemerkt dat sinds het leren van CROSS/OUTER APPLYIk heb mijn querymogelijkheden ook verbeterd met oudere SQL Server-versies, omdat ik veelzijdiger word met de “outer reference”-mogelijkheden van SELECT, ON, en WHERE-clausules!)

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

Het is een stuk korter dan de CASE-instructie.


Antwoord 2, autoriteit 28%

Er is geen garantie dat SQL Server niet zal proberen de CONVERTnaar numeric(20,0)uit te voeren voordathet filter wordt uitgevoerd in de WHERE-clausule.

En zelfs als dat zo was, is ISNUMERICniet voldoende, omdat het £en 1d4herkent als numeriek, geen van beide kan worden geconverteerd naar numeric(20,0).(*)

Verdeel het in twee afzonderlijke query’s, waarvan de eerste de resultaten filtert en in een tijdelijke tabel of tabelvariabele plaatst, waarvan de tweede de conversie uitvoert. (Subquery’s en CTE’s zijn ontoereikend om te voorkomen dat de optimizer de conversie vóór het filter probeert)

Gebruik voor uw filter waarschijnlijk account_code not like '%[^0-9]%'in plaats van ISNUMERIC.


(*) ISNUMERICbeantwoordt de vraag die niemand (voor zover ik weet) ooit heeft willen stellen – “kan deze string worden geconverteerd naar elkevan de numerieke datatypes – maakt niet uit welke?” – wanneer natuurlijk, wat de meeste mensen willen vragen is “kan deze string worden geconverteerd naar x?” waarbij xeen specifiekdoelgegevenstype is.


Antwoord 3, autoriteit 25%

Als u SQL Server 2012 of nieuwer gebruikt, kunt u ook de nieuwe TRY_PARSE()functie:

Retourneert het resultaat van een expressie, vertaald naar het gevraagde gegevenstype, of null als de cast mislukt in SQL Server. Gebruik TRY_PARSE alleen voor het converteren van tekenreeks naar datum/tijd- en getaltypes.

Of TRY_CONVERT/TRY_CAST:

Retourneert een cast-waarde naar het opgegeven gegevenstype als de cast slaagt; anders wordt null geretourneerd.


Antwoord 4, autoriteit 3%

Ik denk dat het probleem niet in de subquery zit, maar in de WHERE-clausule van de buitenste query.
Wanneer u

WHERE account_code between 503100 and 503105

SQL-server zal proberen elke waarde in uw veld Account_code om te zetten in een geheel getal om deze in de opgegeven staat te testen. Uiteraard zal dit niet lukken als er in sommige rijen niet-gehele tekens staan.


Antwoord 5, autoriteit 3%

bedankt,
probeer dit in plaats daarvan

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

Ik help je graag

Other episodes