Rimuovere gli zero finali da decimale in SQL Server

Ho una colonna DECIMAL(9,6) cioè supporta valori come 999,123456.

Ma quando inserisco dati come 123,4567 diventa 123,456700

Come rimuovere quegli zeri?

Un decimal(9,6) memorizza 6 cifre sul lato destro della virgola. Se visualizzare o meno zero finali è una decisione di formattazione, solitamente implementata sul lato client.

Ma dal momento che i formati SSMS vengono spostati senza zeri finali, è ansible rimuovere gli zero finali convertendo il numero decimal in decimal float :

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

stampe:

 123.456700 123,4567 

(Il mio separatore decimale è una virgola, ma i formati SSMS decimali con un punto. Apparentemente un problema noto .)

È ansible utilizzare la funzione FORMAT() (SqlAzure e 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' 

Fai attenzione quando usi FLOAT (o REAL): non usare g17 o più grande (o g8 o più grande con REAL), perché la precisione limitata della rappresentazione della macchina causa effetti indesiderati:

 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' 

Inoltre, si noti che, secondo la documentazione :

FORMAT si basa sulla presenza di .NET Framework Common Language Runtime (CLR). Questa funzione non sarà remotata poiché dipende dalla presenza del CLR. Il comando remoto di una funzione che richiede CLR causerebbe un errore sul server remoto.

Funziona anche in SqlAzure.

 SELECT CONVERT(DOUBLE PRECISION, [ColumnName]) 
 SELECT REVERSE(ROUND(REVERSE(2.5500),1)) 

stampe:

 2.55 
 Cast(20.5500 as Decimal(6,2)) 

dovrebbe farlo

Ho avuto un problema simile, ma è stato anche richiesto di rimuovere il punto decimale dove non era presente alcun decimale, ecco la mia soluzione che divide il decimale nei suoi componenti e basa il numero di caratteri che prende dalla stringa del punto decimale sulla lunghezza di il componente della frazione (senza usare CASE). Per rendere le cose ancora più interessanti, il mio numero è stato memorizzato come un float senza i suoi decimali.

 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') 

Il risultato è doloroso, lo so, ma ci sono arrivato, con molto aiuto dalle risposte di cui sopra.

Il modo migliore NON è la conversione in FLOAT o MONEY prima della conversione a causa del rischio di perdita di precisione. Quindi i modi sicuri possono essere qualcosa del genere:

 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 

dove @value può essere qualsiasi decimale (x, y)

Ero riluttante a lanciare il float a causa del potenziale per un numero maggiore di cifre nel mio decimale rispetto al float

FORMAT quando usato con una stringa di formato .net standard “g8” restituiva la notazione scientifica in caso di decimali molto piccoli (es. 1e-08) che era anche inadatto

L’utilizzo di una stringa di formato personalizzata ( https://docs.microsoft.com/en-us/dotnet/standard/base-types/custom-numeric-format-strings ) mi ha permesso di ottenere ciò che volevo:

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

Se vuoi che il tuo numero abbia almeno uno zero finale, quindi 2.0 non diventa 2, usa una stringa di formato come 0.0#####

Il punto decimale è localizzato, quindi le culture che usano una virgola come separatore decimale incontreranno un output virgola dove il. è

Ovviamente, questa è la pratica scoraggiante di avere il livello dati che esegue la formattazione (ma nel mio caso non esiste un altro livello, l’utente sta letteralmente eseguendo una stored procedure e inserendo il risultato in una email: /)

Avevo bisogno di rimuovere gli zero finali sui miei decimali in modo da poter produrre una stringa di una certa lunghezza con solo zero iniziali

(ad es. avevo bisogno di generare 14 caratteri in modo che 142.023400 diventasse 000000142.0234),

Ho usato parsename , reverse e cast as int per rimuovere gli zeri finali:

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

(Per ottenere i miei zeri iniziali, potrei replicare il numero corretto di zeri in base alla lunghezza di quanto sopra e concatenarlo alla parte anteriore di quanto sopra)

Spero che questo aiuti qualcuno.

è ansible rimuovere gli zeri iniziali e finali in TSQL

  1. Converti in stringa usando la funzione STR TSQL se non stringa, quindi

  2. Rimuovi entrambi gli zeri iniziali e finali

     SELECT REPLACE(RTRIM(LTRIM(REPLACE(AccNo,'0',' '))),' ','0') AccNo FROM @BankAccount 
  3. Maggiori informazioni sul forum .

Prova questo :

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

Fornisce 20.55

Un’altra opzione…

Non so quanto sia efficiente, ma sembra funzionare e non va via float:

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

La linea centrale toglie gli spazi finali, i due esterni rimuovono il punto se non ci sono cifre decimali

Cosa ne pensi di questo? Supponendo che i dati entrino nella tua funzione come @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 

Capisco che questo è un vecchio post, ma vorrei fornire SQL che mi è venuto in mente

 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 

So che questo thread è molto vecchio ma per coloro che non utilizzano SQL Server 2012 o versione successiva o non possono utilizzare la funzione FORMAT per qualsiasi motivo, quindi i seguenti lavori.

Inoltre, molte soluzioni non funzionavano se il numero era inferiore a 1 (ad es. 0,01230000).

Si prega di notare che quanto segue non funziona con i numeri negativi.

 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') 

Restituisce rispettivamente 10,012345 e 0,012345.

prova questo.

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

Il modo più semplice consiste nel CAST il valore come FLOAT e quindi in un tipo di dati stringa.

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

Ho avuto un problema simile, necessario per tagliare gli zeri finali da numeri come xx0000,x00000,xxx000

Ero solito:

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

Codice è il nome del campo con il numero da ritagliare. Spero che questo aiuti qualcun altro.

Una colonna DECIMAL (9,6) convertirà in float senza perdita di precisione, quindi CAST (… AS float) farà il trucco.


@HLGEM: dicendo che float è una scelta sbagliata per la memorizzazione di numeri e “Non usare mai float” non è corretto – devi solo conoscere i tuoi numeri, ad esempio le misure di temperatura andrebbero bene come galleggianti.

@abatishchev e @japongskie: i prefissi di fronte a stored process SQL e le funzioni sono ancora una buona idea, se non richiesto; i link che hai citato indicano solo di non usare il prefisso “sp_” per le stored procedure che non dovresti usare, altri prefissi vanno bene ad esempio “usp_” o “spBob_”

Riferimento: “Tutti gli interi con 6 o meno cifre decimali significative possono essere convertiti in un valore a virgola mobile IEEE 754 senza perdita di precisione”: https://en.wikipedia.org/wiki/Single-precision_floating-point_format

Prova questo:

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

Prova questo:

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