CODEWITHSUNDEEP
sql-servert-sqlformat
sudhakarssd
sudhakarssd

Reputation: 451

How to add leading zeros to decimal value in tsql

i have weight column in a table where weight must be inserted with following format '09.230'. Weight column is of varchar type. so value from front end comes as '9.23' it should get converted to above mentioned format i.e.(09.230). I am able to add trailing zero but adding leading zero is a problem. This is what i have done to add trailing zero

CAST(ROUND(@Weight,3,0) AS DECIMAL (9,3))

Suppose @Weight = 6.56 output with above comes out be '6.560' but output wanted as '06.560'.

Upvotes: 2

Views: 6517

Answers (1)

Heinzi
Heinzi

Reputation: 172270

RIGHT('0'+ CONVERT(VARCHAR, CAST(ROUND(@Weight,3,0) AS DECIMAL (9,3))), 6)

This

  • takes your expression,
  • converts it to a varchar (retaining the trailing zeros, since the source data type was decimal),
  • adds a 0 in front of it, and
  • trims it to 6 characters by removing characters from the front, if needed (e.g. 012.560 -> 12.560, but 06.560 -> 06.560).

Do note, though, that this only works for numbers with at most two digits before the decimal point: 100.123 would be truncated to 00.123!

Upvotes: 3

Related Questions