how to add leading zeroes before and after decimal in sql

Ashwin Chithran 21 Reputation points
2020-11-13T04:31:29.557+00:00

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!!!

Developer technologies | Transact-SQL
{count} votes

4 answers

Sort by: Most helpful
  1. Vaibhav Chaudhari 38,921 Reputation points Volunteer Moderator
    2020-11-13T04:59:28.527+00:00

    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


  2. EchoLiu-MSFT 14,621 Reputation points
    2020-11-13T06:23:47.89+00:00

    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  
    

    39623-image.png

    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

    0 comments No comments

  3. Yitzhak Khabinsky 26,586 Reputation points
    2020-11-13T13:00:16.2+00:00

    How about this:

    DECLARE @myval DECIMAL(15, 10);
    SET @myval = '02345.0000123245';
    
    SELECT FORMAT(@myval, '0000000000.##################');
    
    0 comments No comments

  4. Peter de Bruin-Trautmann 0 Reputation points
    2023-09-13T14:46:47.7066667+00:00

    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);
    
    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.