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 question asked by one of the user (name not disclosed as per request).

Email was received as following:

Dear Pinal,

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

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 use AdventureWorks database for our example purpose. I have noticed that some developers does 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 above two methods.

If you prefer you can create UDF for the same. I would encourage my readers to submit the UDF for satisfying following criteria using 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 first correct answer to this UDF on this blog with due credit.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

About these ads

36 thoughts on “SQL SERVER – Pad Ride Side of Number with 0 – Fixed Width Number Display

  1. I frequently enjoy reading your blog. I maintain several websites using SQLServer backends, but I am not an SQL DBA or expert by any means. With regard to the post about padding numbers with ’0′ is the results picture given in the post the right picture. I thought you were showing us the return values not the code again. But second, when I paste your query into my downloaded version of AdventureWorks I receive error messages on both select statements as follows:

    (10 row(s) affected)
    Msg 102, Level 15, State 1, Line 2
    Incorrect syntax near ‘‘’.
    Msg 102, Level 15, State 1, Line 2
    Incorrect syntax near ‘‘’.

    Am I missing something too simple to see?

    • It is becuase single quotes are represented differently in this blog. Change them to single quotes

      Pinal,

      Why is that all the single quotes are actullay converted to ` in all the posts as well as comments?

  2. CREATE FUNCTION dbo.FnExample
    (
    @Size int,
    @Column_Value varchar(max)
    )
    RETURNS TABLE
    AS
    RETURN
    (
    – Add the SELECT statement with parameter references here
    SELECT RIGHT(REPLICATE(0, @Size)+ Convert (VARCHAR(MAX), @Column_Value), @Size) As ID
    )
    GO

    Sample code to execute :

    SELECT * FROM dbo.FnExample (7, 300)

  3. CREATE FUNCTION [dbo].[f_pad_before](@string VARCHAR(255), @desired_length INTEGER, @pad_character CHAR(1))
    RETURNS VARCHAR(255) AS
    BEGIN

    – Prefix the required number of spaces to bulk up the string and then replace the spaces with the desired character
    RETURN ltrim(rtrim(
    CASE
    WHEN LEN(@string) < @desired_length
    THEN REPLACE(SPACE(@desired_length – LEN(@string)), ‘ ‘, @pad_character) + @string
    ELSE @string
    END
    ))
    END

  4. It might certainly serve the purpose but definitely not the recommendation. DB Server is for returning and storing the data but how the data will be shown is _complete_ responsibility of the _UI_

    • @Kazi Manzur Rashid,

      You have good point. I believe that all the string operations should be done through application as they are much faster to complete there.

      Regards,
      Pinal

  5. If you have to do it, the following UDF will pad the value to the left, but return the WHOLE value if the @PadWidth value is less than the actual length of the @Value (say a @PadWidth of 5 when the @Value is 123456789). Without that check, the original code posted would potentially remove significant digits from the result, which could be Very Bad.

    CREATE FUNCTION dbo.PadLeft (@Value int, @PadWidth int, @PadChar char(1))
    RETURNS varchar(255)
    AS
    BEGIN
    SELECT ISNULL(REPLICATE(@PadChar, @PadWidth – len(@Value)), ”) + CAST(@Value as varchar)
    END

  6. Pingback: SQL SERVER - UDF - Pad Ride Side of Number with 0 - Fixed Width Number Display Journey to SQL Authority with Pinal Dave

  7. Pingback: SQL SERVER - Leading Zero to Number Journey to SQL Authority with Pinal Dave

  8. with null check the following works fine

    select right(’000000′+isnull( MasSeries.CurrSerialNo,’0′),6) from masseries

  9. It worked for me

    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

  10. Hi Pinal,

    I agree with you. By this method it perform very quickly with appropriate answer. I have already used same function like replicate in VFP 9.0 as well as SQL Server with very large database size.

    Reagrds,
    Rajiv Singh

  11. Trying to write this i noticed this method doesn’t work for nchar type so people who are like wondering why doesn’t this work may have to chang the type from nchar to varchar which is easily castable to nvchar.

  12. Pinal,

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

    1 as 100001
    109 as 100109
    0987 as 100987

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

  13. I’m wanting to pad the middle as apposed to either side, how do I stipulate to ingnore the leading character?

    Thanks

    TDB

  14. Mr. Pinal,
    I want to search record from the Mobile_BulkTable in following way. please reply me as early as possible. i have million of record in my table. i want to search record in following manner,
    009198450000
    009198451111
    009198452222
    009198453333
    009198454444
    009198455555
    009198456666
    009198457777
    009198458888
    009198459999
    continuing with 3 digit or 4 digit or 5 digit..
    filter these..

  15. Pingback: SQL SERVER – Preserve Leading Zero While Coping to Excel from SSMS « SQL Server Journey with SQL Authority

  16. Pingback: SQL SERVER – Removing Leading Zeros From Column in Table « SQL Server Journey with SQL Authority

  17. Pingback: SQL SERVER – Weekly Series – Memory Lane – #020 | SQL Server Journey with SQL Authority

  18. 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

  19. 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

  20. 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

  21. 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,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s