LPAD and RPAD in SQL Server using FORMAT

In SQL Server 2012 Microsoft introduced a new T-SQL function FORMAT.  This function returns a value formatted with the specified format and optional culture.  This function can be very useful in formatting Numeric and Date values.  In this post I’ll show you a simple use of FORMAT function to do the padding similar to LPAD and RPAD functions in Oracle.

First lets create some sample data:
--------------------------------------------------------------------------------------------------------
CREATE TABLE #Temp(Id INT)
GO
--Now insert the table Temp with some sample data:
INSERT INTO #Temp SELECT
1 UNION SELECT
2 UNION SELECT
12 UNION SELECT
123 UNION SELECT
1234 UNION SELECT
12345
GO
SELECT ID
FROM #TEMP
--------------------------------------------------------------------------------------------------------

First lets see how to do the LPAD, will talk about RPAD separately because it need some extra efforts.  Will try to do the padding till 5 digits.  Means if there is only 1 digit in column then we need to add extra 4 zeros to the left.  With FORMAT function it actually becomes very very easy.  If you know the syntax of FORMAT function =>> FORMAT ( value, format [, culture ] )  then you just need to put ‘00000’ (number of digits you want to do the padding) at the place of format. 
--------------------------------------------------------------------------------------------------------
SELECT ID ,
FORMAT(ID, '00000') AS LPAD_EXAMPLE
FROM #TEMP
--------------------------------------------------------------------------------------------------------

For the RPAD you need to do little differently.  While defining the format of ‘00000’ we need to add ‘#’ (which represent the value/column) before it:
--------------------------------------------------------------------------------------------------------
SELECT ID ,
FORMAT(ID, '#''00000') AS RPAD_EXAMPLE
FROM #TEMP
--------------------------------------------------------------------------------------------------------

See the combine result of above 2 queries:

For more about the function see: FORMAT (Transact-SQL)

Also check my post about new T-SQL functions introduced in SQL Server see: New T-SQL programming enhancements in SQL Server 2012

In my future posts I’ll try show more different ways we can use FORMAT function.

Thanks
Mangal Pardeshi