CODEWITHSUNDEEP
sql-server-2008
user2618885
user2618885

Reputation: 9

How can I remove trailing and leading zeroes then convert to a decimal without rounding

How can I convert 000000000000000001764705 to 1.76?

Upvotes: 0

Views: 83

Answers (3)

phillyflats
phillyflats

Reputation: 133

Based off of JLampon's comment link to this: 

declare @data as varchar(24)
set @data = '000000000000000001764705'
print round(cast(@data as float) / 1000000,2,1)

Upvotes: 0

ram_sql
ram_sql

Reputation: 404

There are a lot of ways to do it...

select reverse(round(reverse(round(cast(000000000000000001764705 as decimal)/1000000,2)),1))

let me explain: First when you convert it to any decimal or numeric all the leading zero's will truncate as in numeric cases leading zero have no value. second we divided it by 1000000 because as mentioned by user2618885 the decimal will always be 6 positions from the right and then we rounded it up to 2 place after decimal but it will not truncate it to 2 place after decimal instead it will put only the trailing zero's after 2 places of decimal like "1.76000000". third trying to remove the zero's after 2 decimal places by reversing it and then rounding it and again reversing it to original position which can be done in another ways also by using substring, stuff etc... but i used here reverse and round.

Upvotes: 1

Pradeep Kumar
Pradeep Kumar

Reputation: 6979

You can use STUFF to insert the decimal at required position. Then CAST to convert it to decimal.

e.g.

DECLARE @number VARCHAR(255) = '000000000000000001764705'

SELECT CAST(STUFF(@number, len(@number)-5, 0, '.') as DECIMAL(18,2))


/*
RESULT

---------------------------------------
1.76

(1 row(s) affected)

*/

Upvotes: 0

Related Questions