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:
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)