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
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.
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)
42 Comments. Leave new
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?
— Ten digit
Declare @field as varchar(20) = ‘559’
Select Replace(right(replicate(‘ ‘,10) + @field ,10),’ ‘,’0’)
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)
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
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_
This is not about the performacne, by doing the formatting in DB Server, I am putting unnecessary processing cycle as its responsibility was never the data formatting.
Should the title be “..right side” rather than “..ride side”?
The algorithms are for padding *left* side anyway. The description is for *right-justified, zero-filled” or “pad left”.
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
with null check the following works fine
select right(‘000000’+isnull( MasSeries.CurrSerialNo,’0’),6) from masseries
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
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
This Helped Alot – Thanx
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.
Thanks Pinal
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?
@Kumar
Did you read this article ? If not, please read artcile on top of this page.
~IM.
@kumar
Try 100000 + x
Because the 1 at the beginning, it does not have to converted to a string.
I’m wanting to pad the middle as apposed to either side, how do I stipulate to ingnore the leading character?
Thanks
TDB
Thanks :)
Thanks for this post, I was looking for similar solution for few of the queries which I am firing from my SSIS Package where I need to add preceeding Zeros, Your post helped me a lot !
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..
what if I want I need to pad the string on the right side
ex. one hundred thirty two —————
it works, always enjoy reading your blog, ur blog solve most of my problem, thanks a lot