I had written article SQL SERVER – Pad Ride Side of Number with 0 – Fixed Width Number Display few days ago. Previous article was the answer to the email which I had received:
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?
I had asked my blog readers to participate at the end of the article and I have received three very good suggestions. I am going to list all the three suggestions here. For me, all the three suggestions are equally good. I suggest my readers to use any suggestion which they find easy to understand. I suggest to read my previous article before continuing this article further.
Solution 1 by Imran Mohammed
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 -- Run following script to see output SELECT * FROM dbo.FnExample (7, 123) GO
Solution 2 by Fenil Desai
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 GO -- Run following script to see output SELECT dbo.f_pad_before('123',7,'0') GO
Solution 3 by CodeMonkey
CREATE FUNCTION dbo.PadLeft (@Value INT, @PadWidth INT, @PadChar CHAR(1)) RETURNS VARCHAR(255) AS BEGIN RETURN ISNULL(REPLICATE(@PadChar, @PadWidth - LEN(@Value)), '') + CAST(@Value AS VARCHAR) END GO -- Run following script to see output SELECT dbo.PadLeft(123,7,0) GO
Answer of all of the above script will be ‘0000123’.
Again, I want to congratulate all three of my readers to participate in this interesting puzzle and would like to encourage other readers to participate in similar puzzles like this.
Reference: Pinal Dave (http://blog.SQLAuthority.com)