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)
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?
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_
@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
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”?
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
[...] had written article SQL SERVER – Pad Ride Side of Number with 0 – Fixed Width Number Display few days ago. Previous article was answer to the email which I had [...]
[...] SQL SERVER – Pad Ride Side of Number with 0 – Fixed Width Number Display [...]
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 !