Usuń końcowe zera z dziesiętnych w programie SQL Server

Mam kolumnę DECIMAL(9,6)tj. Obsługuje wartości takie jak 999,123456.

Ale kiedy wstawię dane, takie jak 123,4567, staje się 123,456700

Jak usunąć te zera?

A decimal(9,6)przechowuje 6 cyfr po prawej stronie przecinka. To, czy wyświetlać zera końcowe, czy nie, jest decyzją o formatowaniu, zwykle wdrażaną po stronie klienta.

Ale ponieważ formatów SSMS floatbez zer można usunąć końcowe zera przez odlewanie decimalDo float:

select 
    cast(123.4567 as DECIMAL(9,6))
,   cast(cast(123.4567 as DECIMAL(9,6)) as float)

wydruki:

123.456700  123,4567

(Mój separator dziesiętny to przecinek, ale SSMS formatuje po przecinku z kropką. Najwyraźniej znany problem .)

Możesz użyć FORMAT()funkcji (SqlAzure i Sql Server 2012+):

SELECT FORMAT(CAST(15.12     AS DECIMAL(9,6)), 'g18')  -- '15.12'
SELECT FORMAT(CAST(0.0001575 AS DECIMAL(9,6)), 'g10')  -- '0.000158'
SELECT FORMAT(CAST(2.0       AS DECIMAL(9,6)), 'g15')  -- '2'

Zachowaj ostrożność podczas używania z FLOAT (lub REAL): nie używaj g17ani większego ( g8lub większego z REAL), ponieważ ograniczona precyzja reprezentacji maszyny powoduje niepożądane efekty:

SELECT FORMAT(CAST(15.12 AS FLOAT), 'g17')         -- '15.119999999999999'
SELECT FORMAT(CAST(0.9 AS REAL), 'g8')             -- '0.89999998'
SELECT FORMAT(CAST(0.9 AS REAL), 'g7')             -- '0.9'

Ponadto należy pamiętać, że zgodnie z dokumentacją :

FORMAT opiera się na obecności środowiska uruchomieniowego języka wspólnego (CLR) .NET Framework. Ta funkcja nie będzie zdalna, ponieważ zależy od obecności CLR. Zdalne uruchomienie funkcji wymagającej środowiska CLR spowodowałoby błąd na serwerze zdalnym.

Działa również w SqlAzure.

SELECT CONVERT(DOUBLE PRECISION, [ColumnName])

Byłem niechętny rzucaniu na zmiennoprzecinkowe ze względu na możliwość umieszczenia większej liczby cyfr w moim ułamku dziesiętnym niż może to reprezentować liczba zmiennoprzecinkowa

FORMAT w przypadku użycia ze standardowym ciągiem formatu .net 'g8' zwróciło notację naukową w przypadku bardzo małych miejsc po przecinku (np. 1e-08), która również była nieodpowiednia

Użycie niestandardowego ciągu formatu ( https://docs.microsoft.com/en-us/dotnet/standard/base-types/custom-numeric-format-strings ) pozwoliło mi osiągnąć to, co chciałem:

DECLARE @n DECIMAL(9,6) =1.23;
SELECT @n
--> 1.230000
SELECT FORMAT(@n, '0.######')
--> 1.23

Jeśli chcesz, aby twoja liczba miała co najmniej jedno końcowe zero, więc 2.0 nie zmieni się w 2, użyj ciągu formatującego, takiego jak 0.0#####

Punkt dziesiętny jest zlokalizowany, więc kultury używające przecinka jako separatora dziesiętnego napotkają wynik z przecinkiem, w którym. jest

Oczywiście jest to niepożądana praktyka polegająca na tym, że warstwa danych wykonuje formatowanie (ale w moim przypadku nie ma innej warstwy; użytkownik dosłownie uruchamia procedurę składowaną i umieszcza wynik w wiadomości e-mail: /)

SELECT REVERSE(ROUND(REVERSE(2.5500),1))

wydruki:

2.55

Cast(20.5500 as Decimal(6,2))

powinien to zrobić.

Spróbuj tego :

SELECT REPLACE(TRIM(REPLACE(20.5500, "0", " ")), " ", "0")

Daje 20,55

Miałem podobny problem, ale musiałem również usunąć kropkę dziesiętną tam, gdzie nie było miejsca po przecinku, oto moje rozwiązanie, które dzieli liczbę dziesiętną na jej składniki i opiera liczbę znaków pobieranych z ciągu dziesiętnego na długości składnik frakcji (bez użycia CASE). Żeby było jeszcze ciekawiej, moja liczba została zapisana jako liczba zmiennoprzecinkowa bez miejsc po przecinku.

DECLARE @MyNum FLOAT
SET @MyNum = 700000
SELECT CAST(PARSENAME(CONVERT(NUMERIC(15,2),@MyNum/10000),2) AS VARCHAR(10)) 
+ SUBSTRING('.',1,LEN(REPLACE(RTRIM(REPLACE(CAST(PARSENAME(CONVERT(NUMERIC(15,2),@MyNum/10000),1) AS VARCHAR(2)),'0',' ')),' ','0'))) 
+ REPLACE(RTRIM(REPLACE(CAST(PARSENAME(CONVERT(NUMERIC(15,2),@MyNum/10000),1) AS VARCHAR(2)),'0',' ')),' ','0') 

Rezultat jest bolesny, wiem, ale dotarłem tam, z dużą pomocą powyższych odpowiedzi.

Najlepszym sposobem jest NIE konwertowanie na FLOAT lub MONEY przed konwersją ze względu na możliwość utraty precyzji. Tak więc bezpieczne sposoby mogą wyglądać następująco:

CREATE FUNCTION [dbo].[fn_ConvertToString]
(
    @value sql_variant
)
RETURNS varchar(max)
AS
BEGIN
    declare @x varchar(max)
    set @x= reverse(replace(ltrim(reverse(replace(convert(varchar(max) , @value),'0',' '))),' ',0))

    --remove "unneeded "dot" if any
    set @x = Replace(RTRIM(Replace(@x,'.',' ')),' ' ,'.')
    return @x
END

gdzie @ wartość może być dowolną liczbą dziesiętną (x, y)

Miałem podobny problem, potrzebny do przycięcia końcowych zer z liczb takich jak xx0000,x00000,xxx000

Użyłem:

select LEFT(code,LEN(code)+1 - PATINDEX('%[1-Z]%',REVERSE(code))) from Tablename

Kod to nazwa pola z numerem do przycięcia. Mam nadzieję, że to pomoże komuś innemu.

Inna opcja...

Nie wiem, jak wydajne jest to, ale wygląda na to, że działa i nie przechodzi przez float:

select replace(rtrim(replace(
       replace(rtrim(replace(cast(@value as varchar(40)), '0', ' ')), ' ', '0')
       , '.', ' ')), ' ', '.')

Środkowa linia usuwa końcowe spacje, dwie zewnętrzne usuwają kropkę, jeśli nie ma cyfr dziesiętnych

Musiałem usunąć końcowe zera z moich miejsc dziesiętnych, aby móc wyprowadzić ciąg o określonej długości z tylko początkiem zerami

(np. potrzebowałem wypisać 14 znaków, aby 142.023400 stało się 000000142.0234),

Kiedyś parsename, reversea cast as intusunięcie zer:

SELECT
    PARSENAME(2.5500,2)
    + '.'
    + REVERSE(CAST(REVERSE(PARSENAME(2.5500,1)) as int))

(Aby uzyskać moje wiodące zera, mógłbym powtórzyć poprawną liczbę zer w oparciu o długość powyższego i połączyć to na początku powyższego)

Mam nadzieję, że to komuś pomoże.

w TSQL można usunąć zera wiodące i końcowe

1. Przekonwertuj go na ciąg przy użyciu funkcji STR TSQL, jeśli nie na łańcuch, Następnie

2. Usuń zera wiodące i końcowe

   SELECT REPLACE(RTRIM(LTRIM(REPLACE(AccNo,'0',' '))),' ','0') AccNo FROM @BankAccount
   

3. Więcej informacji na forum .

Co powiesz na to? Zakładając, że dane trafiają do Twojej funkcji jako @thisData:

BEGIN
  DECLARE @thisText VARCHAR(255)
  SET @thisText = REPLACE(RTRIM(REPLACE(@thisData, '0', ' ')), ' ', '0')
  IF SUBSTRING(@thisText, LEN(@thisText), 1) = '.'
    RETURN STUFF(@thisText, LEN(@thisText), 1, '')
  RETURN @thisText
END

case when left(replace(ltrim(rtrim(replace(str(XXX, 38, 10), '0',  ' '))), ' ', '0'), 1) = '.'
then '0' 
else ''
end +

replace(ltrim(rtrim(replace(str(XXX, 38, 10), '0',  ' '))), ' ', '0') +

case when right(replace(ltrim(rtrim(replace(str(XXX, 38, 10), '0',  ' '))), ' ', '0'), 1) = '.'
then '0' 
else ''
end

Rozumiem, że to stary post, ale chciałbym udostępnić SQL, który wymyśliłem

DECLARE @value DECIMAL(23,3)
set @value = 1.2000
select @value original_val, 
    SUBSTRING(  CAST( @value as VARCHAR(100)), 
                0,
                PATINDEX('%.%',CAST(@value as VARCHAR(100)))
            )
      + CASE WHEN ROUND( 
                        REVERSE( SUBSTRING( CAST(@value as VARCHAR(100)),
                                        PATINDEX('%.%',CAST(@value as VARCHAR(100)))+1,
                                        LEN(CAST(@value as VARCHAR(100)))
                                        )
                                )
                    ,1) > 0 THEN 
            '.' 
            +  REVERSE(ROUND(REVERSE(SUBSTRING( CAST(@value as VARCHAR(100)),
                                                PATINDEX('%.%',CAST(@value as VARCHAR(100)))+1,
                                                LEN(CAST(@value as VARCHAR(100)))
                                                )
                ),1))
        ELSE '' END  AS modified_val

Spróbuj tego.

select CAST(123.456700 as float),cast(cast(123.4567 as DECIMAL(9,6)) as float)

Najłatwiejszym sposobem jest RELACJA wartości jako FLOAT, a następnie do typu danych łańcuchowych.

CAST(CAST(123.456000 AS FLOAT) AS VARCHAR(100))

Spróbuj tego:

select Cast( Cast( (ROUND( 35.457514 , 2) *100) as Int) as float ) /100

Wiem, że ten wątek jest bardzo stary, ale dla tych, którzy nie używają programu SQL Server 2012 lub nowszego lub nie mogą używać funkcji FORMAT z jakiegokolwiek powodu, działa to następująco.

Ponadto wiele rozwiązań nie działało, jeśli liczba była mniejsza niż 1 (np. 0,01230000).

Należy pamiętać, że poniższe czynności nie działają z liczbami ujemnymi.

DECLARE @num decimal(28,14) = 10.012345000
SELECT PARSENAME(@num,2) + REPLACE(RTRIM(LTRIM(REPLACE(@num-PARSENAME(@num,2),'0',' '))),' ','0') 

set @num = 0.0123450000
SELECT PARSENAME(@num,2) + REPLACE(RTRIM(LTRIM(REPLACE(@num-PARSENAME(@num,2),'0',' '))),' ','0') 

Zwraca odpowiednio 10,012345 i 0,012345.

Spróbuj tego:

select isnull(cast(floor(replace(rtrim(ltrim('999,999.0000')),',','')) as int),0)

Kolumna DECIMAL (9,6) zostanie przekonwertowana na zmiennoprzecinkową bez utraty precyzji, więc CAST (... AS float) załatwi sprawę.

@HLGEM: mówienie, że zmiennoprzecinkowy jest złym wyborem do przechowywania liczb i „Nigdy nie używaj liczby zmiennoprzecinkowej” jest niepoprawne - wystarczy znać swoje liczby, np. Pomiary temperatury byłyby niezłe.

@abatishchev i @japongskie: przedrostki przed zapisanymi procedurami i funkcjami SQL są nadal dobrym pomysłem, jeśli nie są wymagane; wspomniane łącza instruują tylko, aby nie używać przedrostka „sp_” dla procedur składowanych, których nie należy używać, inne prefiksy są w porządku, np. „usp_” lub „spBob_”

Odniesienie: „Wszystkie liczby całkowite zawierające 6 lub mniej znaczących cyfr dziesiętnych można konwertować na wartości zmiennoprzecinkowe IEEE 754 bez utraty precyzji”: https://en.wikipedia.org/wiki/Single-precision_floating-point_format