SQL SERVER – Pad Ride Side of Number with 0 – Fixed Width Number Display

Today we will look something which is very quick and but quite frequently useful string operation over numeric datatype. This article is written based on a question asked by one of the users (name not disclosed as per request). Let us see how to show a fixed width number display.

Email was received as following:

Dear Pinal,

I have situation where I need to display my numbers in fixed format. Like

Solarwinds

1 as 0000001
109 as 0000109

0987 as 0000987

Is there any string function in SQL Server which can do this?

There is no ready-made function in SQL Server which offers this solution but we can quickly write up something very simple. I have used the AdventureWorks database for our example purpose. I have noticed that some developers do not use CAST function but I strongly suggest to use it as if CAST function is not used and your column is of numeric datatype it will give errors.

USE AdventureWorks
GO
SELECT TOP 10 ContactID
FROM Person.Contact
GO
/* Method 1 Using RIGHT function*/
SELECT TOP 10 RIGHT('0000000'
+ CAST(ContactID AS VARCHAR(7)), 7) PaddedContactID
FROM Person.Contact
GO
/* Method 2 Using RIGHT AND REPLICATE function*/
SELECT TOP 10 RIGHT(REPLICATE('0', 7)
+ CAST(ContactID AS VARCHAR(7)), 7) PaddedContactID
FROM Person.Contact
GO

Following image demonstrates the result of the above two methods.

SQL SERVER - Pad Ride Side of Number with 0 - Fixed Width Number Display pading0

If you prefer you can create UDF for the same. I would encourage my readers to submit the UDF for satisfying the following criteria using the code provided above. UDF should take a parameter ‘Total Width of Number (in our example it is 7).

I will publish the reader who posts the first correct answer to this UDF on this blog with due credit.

Reference : Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
, , , ,
Previous Post
SQLAuthority News – Author Visit – Complete Wrapup of Microsoft MVP Summit 2009 Trip
Next Post
SQL SERVER – Difference Between Union vs. Union All – Optimal Performance Comparison

Related Posts

42 Comments. Leave new

  • Liung Hartono
    April 4, 2013 12:16 pm

    Dear Pinal,

    I have tried this query :
    select right(‘000’ + cast(serial_no as varchar(3)),3) from MyTable
    also this query:
    select right(‘000’ + convert(varchar(3), serial_no),3) from MyTable
    but it keep displaying results without leading zero.
    I wonder what’s wrong, is there something in server side I should change?

    Thanks

    Reply
  • select right(‘1000’ + convert(varchar(3), no_Job_req),3) from coba

    Reply
  • Dear Pinal,

    I have tried this query :
    select right(’000′ + cast(serial_no as varchar(3)),3) from MyTable
    also this query:
    select right(’000′ + convert(varchar(3), serial_no),3) from MyTable
    but it keep displaying results without leading zero.
    I wonder what’s wrong, is there something in server side I should change?

    Thanks

    Reply
  • update tablename

    set

    BOOK_NO = +’0’+BOOK_NO // add number of zero you want
    BOOK_NO = +’00’+BOOK_NO // add number of zero you want
    BOOK_NO = +’000’+BOOK_NO // add number of zero you want
    BOOK_NO = +’0000’+BOOK_NO // add number of zero you want
    BOOK_NO = +’00000’+BOOK_NO // add number of zero you want
    BOOK_NO = +’000000’+BOOK_NO // add number of zero you want
    BOOK_NO = +’0000000’+BOOK_NO // add number of zero you want

    where LEN(BOOKINW.BOOK_NO) =6 // if you want to specify any condiions

    Reply
  • Dear Saineshwar,

    Thanks for your suggestion, it helps :)
    I was focusing in one line command, meanwhile I should be able to do it in simple ways although I can’t do it in 1 line.

    Best Regards,

    Reply
  • sriharsha mangamuri
    May 30, 2013 11:07 am

    i have tryed ur query but it is not working

    Reply
  • Hi,

    I am trying to get 15.6 to look like this: 0000015600000. Any thoughts on how I can pad both sides, without the decimal?

    Thanks

    Reply
  • Hi, I need some help with this script. I have the following in my connection string and it returns ‘201511’ instaed of ‘20150101’. What changes do I need to make to get the required.

    (DT_STR,4,1252)YEAR( DATEADD( “dd”, -1, getdate() ))+(DT_STR,4,1252)MONTH( DATEADD( “dd”, -1, getdate() ))+(DT_STR,4,1252)DAY( DATEADD( “dd”, -1, getdate() ))+”.dmp”+”\\”+”comm_scheme”+”.txt”

    Reply
  • much simpler solutions available in SQL – use the ancient STR function – been there since SQL4.0

    DECLARE @len SMALLINT = 16
    DECLARE @value INT = 1234

    SELECT REPLACE(STR(@value,@len),’ ‘,’0’)

    — and the ‘0’ chould be any padding character

    Reply
  • Questiom. I tried to use the right fnction on sql workbench, but it seems it doesn’t accept it…do you know why?

    Reply
  • I need some assistance as I am new to T-SQL and still learn my way around I have a field value

    6.2.1 HF 2

    Which I need to strip the HF and add leading zeros to only where it’s a single digit number and then remove the ‘.’

    So as an example I need it to look like 06020102

    or 6.2.12 HF 2 it would then need to look like 06021202

    Can anyone assist me in how I can accomplish this, thanks in advance

    Reply
  • It is difficult to get the output in a simple straight forward query
    ( as i tried in my post

    To get the required output, a UDF has been used

    —————- UDF to parse string —————-
    CREATE FUNCTION [dbo].[udf-Str-Parse] (@String varchar(max),@Delimiter varchar(10))
    Returns Table
    As
    Return (
    Select RetSeq = Row_Number() over (Order By (Select null))
    ,RetVal = LTrim(RTrim(B.i.value(‘(./text())[1]’, ‘varchar(max)’)))
    From (Select x = Cast(”+ Replace(@String,@Delimiter,”)+” as xml).query(‘.’)) as A
    Cross Apply x.nodes(‘x’) AS B(i)
    );
    —————- UDF to parse string —————-

    Select A.*
    ,NewField = B.String
    From Sample A
    Cross Apply (
    Select String = ltrim((Select cast(RetVal as varchar(25))
    From (Select RetSeq,RetVal=Right(’00’+RetVal,2)
    From [dbo].[udf-Str-Parse](replace(Field,’ ‘,’.’),’.’)
    Where Try_Convert(int,RetVal)>=0 ) A
    For XML Path (”)))
    ) B

    Reply
  • Thank you Pinal Dave for always having such great tutorials. This site has helped me so many times.

    Reply
  • No built in field yet in sql server 2016 which can do this on it’s own?

    Reply

Leave a Reply

Menu