how to add leading zeroes before and after decimal in sql

Question

Hi ,

Can somebody help me in writing the query to pad zeros for a decimal values.
At the moment it is with 00000.0000000000 ( with a length of 5.10 )

I want to write a statement to convert them to 0000000000.00000000000000000000 ( with a length of 10.20 )

Another example : 02345.0000123245 will change to 0000002345.00000000000000123245

Thanks in advance!!!

Answer 1

Does below work?

declare @myval DECIMAL(15,10)
set @myval = '02345.0000123245'

select format(@myval,'0000000000.00000000000000000000')

---

Please don't forget to Accept Answer and Up-vote if the response helped -- Vaibhav

Answer 2

Hi @Ashwin Chithran ，

declare @myval DECIMAL(15,10)  
set @myval = '02345.0000123245'  
;with cte1  
as(select format(@myval,'0000000000.0000000000') as col1)  
,cte2   
as (select col1,right(col1,len(col1)-charindex('.',col1 )) col2 from cte1)  
  
select left(col1,charindex('.',col1 ))+left('00000000000000000000',20-len(col2))+col2 from cte2  

If you have any question, please feel free to let me know.
If the response is helpful, please click "Accept Answer" and upvote it.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues November--What can I do if my transaction log is full?
Hot issues November--How to convert Profiler trace into a SQL Server table

Answer 3

How about this:

DECLARE @myval DECIMAL(15, 10);
SET @myval = '02345.0000123245';

SELECT FORMAT(@myval, '0000000000.##################');

Answer 4

FORMAT is the easiest way, but may have a severe performance impact when used inside a heavy query.

This is because FORMAT is to be handled by the external .Net library, and the mere occurence of FORMAT within a query blocks some query optimizations.

The following solution (for non-negative numbers) uses only internal functions, and may be much more efficient:

DECLARE @myval DECIMAL(15, 10) = 02345.0000123245;
-- SELECT FORMAT(@myval, '0000000000.00000000000000000000');
SELECT right('0000000000' + convert(varchar(32), convert(decimal(30,20), @myval)), 31);