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.
