SQL Chyba – Conversion failed when converting the varchar value to data type int

Chybu Conversion failed when converting the varchar value to data type int vypíše SQL Server v momentě, kdy se snažíme hodnotu, která je uložena jako datový typ varchar (text) konvertovat na datový typ INTEGER. To nemusí být problém pokud máme jako varchar uložena čísla, ale problém nastane pokud se nám mezi tato čísla zamíchá nějaký text.

Pokud vás zajímá problém více do hloubky tak čtěte dále. SQL Server totiž umí udělat konverzi datového typu varchar (text) na číslo pokud v něm je uloženo něco, co lze převést na číslo. Text ovšem na číslo převést nelze a proto se vrácena chyba. Ukážu na příkladu:

(1) Založíme si tabulku a sloupec s datovým typem varchar a vložíme do ní textové hodnoty 1,2,3,4,5

CREATE TABLE [dbo].[Test_ISNUMERIC] (
[Hodnota] VARCHAR(255)
);

INSERT INTO [dbo].[Test_ISNUMERIC] ([Hodnota])
VALUES ('1'),('2'),('3'),('4'),('5');

(2) Jak jsem popsal můžeme udělat konverzi na číslo a nad hodnotami provést agregační operaci, třeba SUM. To je v pohodě, protože v tabulce máme pouze hodnoty, které lze konvertovat na číslo a výsledkem je 15.

Suma textových hodnot

(3) Vložme do tabulky další 2 textové hodnoty a proveďmě stejný dotaz ještě jednou. A je to tady, dostáváme chybu

Conversion failed when converting the varchar value to data type int

Řešení pochopitelně existuje a pokud bychom chtěli provést sumu pouze číselných hodnot, můžeme využít funkci ISNUMERIC(). Tato funkce otestuje hodnoty a vrací 1 pokud je hodnota číslo a 0 pokud hodnota číslo není. Z toho plyne že si můžeme udělat jednoduchy CASE WHEN, kdy za nečíselné hodnoty doplníme nulu.

SELECT
   [Hodnota]
  ,CASE
     WHEN ISNUMERIC([Hodnota]) = 1 THEN [Hodnota]
     ELSE 0
   END                       AS [Test_na_cislo]
FROM [dbo].[Test_ISNUMERIC];

 

ISNUMERIC

Vidíme, že u textových hodnot byla doplněna 0. Sloupec Test_na_cislo se dá už v pohodě sečíst a pokud obalíme CASE agregační funkcí SUM, tak dosáhneme výsledku 15.

ISNUMERIC, agregace

 

_______________________________________________________________________________________________
Intelligent technologies - podniková řešení a školení
SQL Chyba – Conversion failed when converting the varchar value to data type int
Hodnocení

2 thoughts on “SQL Chyba – Conversion failed when converting the varchar value to data type int

  1. Honza C

    Ahoj

    jenže ono to (is numeric) ne nutně vždy funguje spolehlivě, viz příklad:

    declare @cislo varchar(10) = ‘1,0’
    select isnumeric(@cislo) — zobrazi 1
    select cast(@cislo as float) — spadne

    srdečně doporučuji tohle:

    select try_cast(@cislo as float)

    TRY_CAST, TRY_CONVERT … užitečné zvlášť také pro datumy …

    https://www.c-sharpcorner.com/UploadFile/manas1/tryparse-tryconvert-and-trycast-in-sql-server/

    lze tam specifikovat “jak” se to má konvertovat …

  2. Honza Zedníček Post author

    Ahoj Honzo, floaty můžeš taky přecastovat, akorát jsi tam měl místo tečky čárku.

    DECLARE @cislo VARCHAR(10) = ‘1.3’
    SELECT ISNUMERIC(@cislo), CAST(@cislo AS FLOAT)

    vrátí 1 a 1,3

    ale fajn postřeh, funkce TRY_ jsou pro tyto účely naprosto ideální řešení a CASE je řekněme méně elegantní 🙂

Napsat komentář

Vaše emailová adresa nebude zveřejněna.

This site uses Akismet to reduce spam. Learn how your comment data is processed.