Remove trailing 0 when CAST as NVarChar

Question

I have data as Decimal(15,4) and values can be like 14.0100, or 14.0000, or 14.9999

For integration with other system we have to store this kind of data in NVarChar(MAX) attributes table. When I run CAST(Field AS NVarChar(MAX)) I get string values like 0.0000

What I want is to trim trailing zeros (and period if needed) from those strings because data later used in online transmission and it's much better to send 14 instead of 14.0000

How do I do that?

Lukasz Szozda · Accepted Answer · 2016-01-13 19:22:24Z

SQL Server 2012+ you could use FORMAT, with SQL Server 2008 you could use string manipulation:

CREATE TABLE #tab(col DECIMAL(15,4)); INSERT INTO #tab(col) VALUES (14.0100), (14.0000), (14.9999), (10), (0), (-1), (-10), (-12.01), (-12.10); SELECT col ,result_2012 = FORMAT(col, '########.####') ,result_2008 = CASE WHEN col = 0 THEN '0' ELSE LEFT(col,LEN(col) - CASE WHEN PATINDEX('%[1-9]%', REVERSE(col)) < PATINDEX('%[.]%', REVERSE(col)) THEN PATINDEX('%[1-9]%', REVERSE(col)) - 1 ELSE PATINDEX('%[.]%', REVERSE(col)) END) END FROM #tab;

LiveDemo

MightyRearranger · Accepted Answer · 2016-01-13 17:53:09Z

You could try casting the Decimal numbers to floats before casting then casting them to NVarChars i.e. CAST(CAST(Field as Float(8)) as NVarChar(MAX)). The only issue you might have with this is if any of your existing numbers have a greater precision than an 8 byte float can deal with which should be easy enough to check for.

It would also be possible to remove the extra zeros after you've casted the numbers as strings using case statements, i.e.

case when right(Field, 4) = '0000' then left(Field, len(Field) - 5) -- -5 to remove the decimal point else case when right(Field, 3) = '000' Then left(Field, len(Field) - 3) else case when right(Field, 2) = '00' Then left(Field, len(Field) - 2) else case when right(Field, 1) = '0' Then left(Field, len(Field) - 1) end end end end

I like Float(8) idea, what is max precision? Some decimals we use are(18,15)
I think if you use float(53) it can in theory be used for up to 15 digits, but due to the nature of the way they're stored they aren't always 100% precise. You could try a select * from table where field <> cast(field as float(53)) to see if there are any numbers it doesn't convert accurately.
FLOAT is inaccurate datatype. Mixing it with exact datatype (DECIMAL) is not best idea.
Yes, I will check. Per documentation Float is 53 by default. But I remember I've seen weird conversions before, I never use Float in my code..
If you have decimals with different precision and accuracy is important a better way of going about it might be to write a recursive function that keeps checking and removing the last character of a string until it doesn't equal '0'.

Atheer Mostafa · Accepted Answer · 2016-01-13 20:33:02Z

simple casting to REAL then back to varch(max) if you want will remove the leading 0s as follows:

declare @a as Decimal(15,4)=14.0010 select cast(cast (@a as real) as varchar(max)) as a2 -- ouptut is 14.001 declare @a as Decimal(15,4)=14.0000 select cast(cast (@a as real) as varchar(max)) as a2 -- output is 14

As always simple and not entirely correct: declare @a as Decimal(15,4)=1000014.0010 select cast(cast (@a as real) as varchar(max)) as a2 Demo Result 1.00001e+006
nice tip @lad ... I have made another "simple" and correct Answer, kindly review it below. Thanks

Atheer Mostafa · Accepted Answer · 2016-01-14 15:52:28Z

The Idea here is to use the built in string function of Trimming spaces to TRIM ZEROs from the converted decimal as follows:

declare @d as Decimal(15,4)=11400012.00000, @s0 VARCHAR(MAX), @s VARCHAR(MAX) set @s0=cast(@d as varchar(max)) set @s=left(@s0,len(rtrim(replace(@s0,'0',' ')))) if right(@s,1)='.' set @s=left(@s,len(@s)-1) select @s -- returns 11400012.001

You also can define the function to do this task for you:

CREATE FUNCTION TRIM0(@s0 varchar(max)) RETURNS varchar(max) AS BEGIN declare @s varchar(max)='' set @s=left(@s0,len(rtrim(replace(@s0,'0',' ')))) if right(@s,1)='.' set @s=left(@s,len(@s)-1) return @s END

As always almost simple but not entirely correct 😎 Don't get me wrong. It is very good you try to be helpful, but please do basic test before you post your solution. Counter example: 11400000.0000 -> 114. Demo
@lad did you check how simple solutions could be correct!! :)
Yes it is very convinent to write custom UDF for Ad-hoc queries :). All your simple solution needs to be wrapped by function, how would you use it for multiple rows at once without UDF? Anyway simple solution is to upgrade to decent version of SQL Server and use built-in FORMAT

GigiS · Accepted Answer · 2016-01-15 09:33:14Z

--With a lot of fantasy --Tip: to optimize performances, first select values in a @-table, then do this transformation. select left( cast(Field as varchar(max)), charindex('.', cast(Field as varchar(max))) - 1) + Case when reverse(cast(cast(reverse(substring(cast(Field as varchar), charindex('.', cast(Field as varchar)) + 1 ,4)) as int) as nvarchar)) = 0 Then '' Else '.' + reverse(cast(cast(reverse(substring(cast(Field as varchar), charindex('.', cast(Field as varchar)) + 1 ,4)) as int) as nvarchar)) End

Right. I forgot a reverse. Now is correct (if 14.0 is expected value).

Collectives™ on Stack Overflow

Remove trailing 0 when CAST as NVarChar

5 Answers 5

Comments

8 Comments

2 Comments

3 Comments

4 Comments

Hot Network Questions