VARCHAR în conversia DECIMAL (Programare, Sql, Server Sql, Sql Server 2008)

Biju Thomas a intrebat.

Vreau să convertesc un varchar(max) coloană în decimal(10,4).

Când încerc să folosesc cast sau convert primesc o excepție de depășire aritmetică. Problema este că datele stocate în coloana varchar pot conține diferite precizii și scale diferite. De exemplu, 123456789.1234567″, 1.12345678 sau 123456.1234.

Pentru valori precum 123456.1234, conversia se face fără probleme, dar pentru alte valori am unele probleme.

Orice ajutor ar fi apreciat.

Comentarii

  • Cum vă așteptați 123456789.1234567 să se potrivească într-un DECIMAL(10,4)? –  > Por Aaron Bertrand.
  • Trebuie să fie o zecimală sau puteți folosi în schimb un tip de date float sau real: msdn.microsoft.com/en-us/library/ms173773.aspx –  > Por Mark Kram.
  • dacă nu se potrivește, atunci vreau să identifice asta. am nevoie doar de o modalitate ca try_parse în sql 2012, dar din păcate versiunea serverului sql este 2008 –  > Por Biju Thomas.
  • și care este problema pe care o aveți cu celelalte valori? –  > Por codingbiz.
  • Obținerea unei erori de depășire aritmetică –  > Por Biju Thomas.
12 răspunsuri
codingbiz

După ce am testat, am constatat că nu a fost locul zecimal care a cauzat problema, a fost precizia (10)

Acest lucru nu funcționează: Eroare de depășire aritmetică de depășire a limitei convertirii varchar în tipul de date numeric.

DECLARE @TestConvert VARCHAR(MAX) = '123456789.12343594'

SELECT CAST(@TestConvert AS DECIMAL(10, 4))

A funcționat

DECLARE @TestConvert VARCHAR(MAX) = '123456789.12343594'

SELECT CAST(@TestConvert AS DECIMAL(18, 4))

Jeremy Pridemore

Explicația mea este în cod. 🙂

DECLARE @TestConvert VARCHAR(MAX) = '123456789.1234567'
BEGIN TRY
    SELECT CAST(@TestConvert AS DECIMAL(10, 4))
END TRY
BEGIN CATCH
    SELECT 'The reason you get the message "' + ERROR_MESSAGE() + '" is because DECIMAL(10, 4) only allows for 4 numbers after the decimal.'
END CATCH

-- Here's one way to truncate the string to a castable value.
SELECT CAST(LEFT(@TestConvert, (CHARINDEX('.', @TestConvert, 1) + 4)) AS DECIMAL(14, 4))

-- If you noticed, I changed it to DECIMAL(14, 4) instead of DECIMAL(10, 4) That's because this number has 14 digits, as proven below.
-- Read this for a better explanation as to what precision, scale and length mean: http://msdn.microsoft.com/en-us/library/ms190476(v=sql.105).aspx
SELECT LEN(LEFT(@TestConvert, (CHARINDEX('.', @TestConvert, 1) + 4)))

Sasha

Am ajuns la următoarea soluție:

SELECT [Str], DecimalParsed = CASE 
WHEN ISNUMERIC([Str]) = 1 AND CHARINDEX('.', [Str])=0 AND LEN(REPLACE(REPLACE([Str], '-', ''), '+', '')) < 29 THEN CONVERT(decimal(38,10), [Str])
WHEN ISNUMERIC([Str]) = 1 AND (CHARINDEX('.', [Str])!=0 AND CHARINDEX('.', REPLACE(REPLACE([Str], '-', ''), '+', ''))<=29) THEN 
    CONVERT(decimal(38,10), 
            CASE WHEN LEN([Str]) - LEN(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE([Str], '0', ''), '1', ''), '2', ''), '3', ''), '4', ''), '5', ''), '6', ''), '7', ''), '8', ''), '9', '')) <= 38 
                 THEN [Str] 
                 ELSE SUBSTRING([Str], 1, 38 + LEN(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE([Str], '0', ''), '1', ''), '2', ''), '3', ''), '4', ''), '5', ''), '6', ''), '7', ''), '8', ''), '9', ''))) END)
ELSE NULL END
FROM TestStrToDecimal

Știu că pare exagerată și probabil că este, dar pentru mine funcționează (a verificat atât numere pozitive, cât și negative, mari și mici, de diferite precizii și scări – totul este convertit în decimal(38,10) sau NULL).

Este codificat în mod greșit la decimal(38,10) tip, așa că, dacă aveți nevoie de o precizie diferită, schimbați constantele din cod (38, 10, 29).

Cum funcționează? Rezultatul este:

  • dacă conversia este simplă, fără depășire sau pierdere de precizie (de exemplu, 123 sau 123,456), atunci doar o convertește.
  • dacă numărul nu este prea mare, dar are prea multe cifre după virgulă (de exemplu, 123.12345678901234567890123456789012345678901234567890), atunci taie cifrele care depășesc la sfârșit, păstrând doar primele 38 de cifre.
  • în cazul în care numărul este prea mare și nu poate fi convertit în zecimale fără să se producă o depășire (de exemplu, 98765432109876543210987654321098765432109876543210), atunci se returnează NULL.

fiecare caz este o instrucțiune WHEN separată în codul de mai sus.

Iată câteva exemple de conversie:

user2714866

Vă scapă faptul că 6.999,50 nu este o zecimală validă. Nu poți avea o virgulă și o zecimală într-o valoare zecimală, nu-i așa? Ce număr ar trebui să fie?

Presupunând că localitatea dvs. specifică . ca grupare și , ca separator zecimal: Pentru a elimina cifrele de grupare:

SELECT CONVERT(decimal(11,2), REPLACE(‘6.999,50’, ‘.’, ”))

se va obține 6999,50 ca zecimală

dodexaedru

Va trebui să trunchiați singuri valorile ca șiruri de caractere înainte de a le introduce în coloana respectivă.

În caz contrar, dacă doriți mai multe zecimale, va trebui să modificați declarația coloanei zecimale.

Mark Kram

Încă nu ați explicat de ce nu puteți utiliza un tip de date Float, așa că iată un exemplu:

DECLARE @StringVal varchar(50)

SET @StringVal = '123456789.1234567'
SELECT @StringVal, CAST(@StringVal AS FLOAT)

SET @StringVal = '1.12345678'
SELECT @StringVal, CAST(@StringVal AS FLOAT)

SET @StringVal = '123456.1234'
SELECT @StringVal, CAST(@StringVal AS FLOAT)

Comentarii

  • Nu pot folosi float sau orice alt tip de date deoarece coloana vizată este de tip Decimal(10,4) pe care nu o pot modifica. –  > Por Biju Thomas.
Bill

Problema dvs. majoră nu este reprezentată de lucrurile din dreapta zecimalei, ci de cele din stânga. Cele două valori din declarația dvs. de tip sunt precision și scale.

Din MSDN: „Precizia este numărul de cifre dintr-un număr. Scala este numărul de cifre din dreapta punctului zecimal dintr-un număr. De exemplu, numărul 123.45 are o precizie de 5 și o scară de 2.”

Dacă specificați (10, 4), înseamnă că puteți stoca doar 6 cifre în stânga punctului zecimal, sau un număr maxim de 999999.9999. Orice valoare mai mare decât aceasta va provoca o depășire.

Biju Thomas

Implementat cu ajutorul funcției personalizate. Aceasta va verifica dacă valoarea șirului de caractere poate fi convertită în siguranță în zecimală

CREATE FUNCTION [dbo].[TryParseAsDecimal]
(
    @Value      NVARCHAR(4000)
    ,@Precision INT
    ,@Scale     INT
)

RETURNS BIT
AS
BEGIN

    IF(ISNUMERIC(@Value) =0) BEGIN
        RETURN CAST(0 AS BIT)
    END
    SELECT @Value = REPLACE(@Value,',','') --Removes the comma

    --This function validates only the first part eg '1234567.8901111111'
    --It validates only the values before the '.' ie '1234567.'
    DECLARE @Index          INT
    DECLARE @Part1Length    INT 
    DECLARE @Part1          VARCHAR(4000)   

    SELECT @Index = CHARINDEX('.', @Value, 0)
    IF (@Index>0) BEGIN
        --If decimal places, extract the left part only and cast it to avoid leading zeros (eg.'0000000001' => '1')
        SELECT @Part1 =LEFT(@Value, @Index-1);
        SELECT @Part1=SUBSTRING(@Part1, PATINDEX('%[^0]%', @Part1+'.'), LEN(@Part1));
        SELECT @Part1Length = LEN(@Part1);
    END
    ELSE BEGIN
        SELECT @Part1 =CAST(@Value AS DECIMAL);
        SELECT @Part1Length= LEN(@Part1)
    END 

    IF (@Part1Length > (@[email protected])) BEGIN
        RETURN CAST(0 AS BIT)
    END

    RETURN CAST(1 AS BIT)

END

Comentarii

  • Sunteți un salvator de vieți! Vă mulțumesc foarte mult. 🙂 –  > Por Annie Lagang.
DDuffy

Știu că este o întrebare veche, dar Bill pare să fie singurul care a „explicat” cu adevărat problema. Toți ceilalți par să vină cu soluții complexe la o utilizare greșită a unei declarații.

„Cele două valori din declarația ta de tip sunt precision și scale”.

„Dacă specificați (10, 4), înseamnă că puteți stoca doar 6 cifre în stânga zecimalei, sau un număr maxim de 999999.9999. Orice valoare mai mare decât aceasta va provoca o depășire.”

Așadar, dacă declarați DECIMAL(10,4) puteți avea un total de 10 numere, cu 4 dintre ele venind DUPĂ virgulă. deci 123456.1234 are 10 cifre, 4 după virgulă. Acest lucru se va încadra în parametrii DECIMAL(10,4).1234567.1234 va genera o eroare. 11 cifre trebuie să încapă într-un spațiu de 10 cifre, iar 4 cifre TREBUIE să fie folosite DUPĂ virgulă. Tăierea unei cifre din partea stângă a zecimalei nu este o opțiune. Dacă cele 11 caractere ar fi fost 123456.12345, nu s-ar fi produs o eroare, deoarece tăierea (rotunjirea) de la sfârșitul unei valori zecimale este acceptabilă.

Atunci când declarați zecimale, încercați întotdeauna să declarați valoarea maximă pe care coloana dvs. o va utiliza în mod realist și numărul maxim de zecimale pe care doriți să le vedeți. Astfel, dacă coloana dvs. va afișa doar valori cu un maxim de 1 milion și vă interesează doar primele două zecimale, declarați ca fiind DECIMAL(9,2)Astfel, veți obține un număr maxim de 9.999.999,99 înainte de a se produce o eroare.

Înțelegerea problemei înainte de a încerca să o remediați vă va asigura că alegeți soluția corectă pentru situația dumneavoastră și vă va ajuta să înțelegeți motivul pentru care soluția este necesară/ funcționează.

Din nou, știu că am întârziat cinci ani la petrecere. Cu toate acestea, cei doi cenți ai mei cu privire la o soluție pentru acest lucru, (judecând după comentariile dvs. că coloana este deja setată ca fiind DECIMAL(10,4) și nu poate fi schimbată)Cel mai simplu mod de a face acest lucru ar fi în doi pași. Verificați dacă zecimala nu este mai departe de 10 puncte, apoi tăiați la 10 cifre.

CASE WHEN CHARINDEX('.',CONVERT(VARCHAR(50),[columnName]))>10 THEN 'DealWithIt'
ELSE LEFT(CONVERT(VARCHAR(50),[columnName]),10) 
END AS [10PointDecimalString]

Motivul pentru care am lăsat acest lucru ca un șir de caractere este pentru a vă putea ocupa de valorile care au peste 10 cifre în stânga zecimalei.

Dar este un început.

Moises Conejo
create function [Sistema].[fParseDecimal]
(
    @Valor nvarchar(4000)
)
returns decimal(18, 4) as begin
    declare @Valores table (Valor varchar(50));

    insert into @Valores values (@Valor);

    declare @Resultado decimal(18, 4) = (select top 1 
        cast('' as xml).value('sql:column("Valor") cast as xs:decimal ?', 'decimal(18, 4)')
    from @Valores);
    
    return @Resultado;
END

aleb

În cazul în care aveți nevoie să rotunjiți rezultatul, nu să îl trunchiați, puteți folosi acest lucru:

select convert(decimal(38,4), round(convert(decimal(38,10), '123456789.1234567'),4))

Aceasta va returna următoarele:

'123456789.1235' for '123456789.1234567'
'123456789.1234' for '123456789.1234467'

Shahadat Hossain Khan

În MySQL

select convert( if( listPrice REGEXP '^[0-9]+$', listPrice, '0' ), DECIMAL(15, 3) ) from MyProduct WHERE 1