RPAD and LPAD Functions in Sql Server

Edit: If you are using SQL Server 2012 or 2014 you may like to see my new post on this topic: LPAD and RPAD Functions in SQL Server

In Sql Server we don’t have RPAD and LPAD functions as they are in Oracle. But it is not that difficult to implement the same thing in Sql Server. With the help of RIGHT and LEFT functions we can achieve that easily. Let’s see how…

First create this table:
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

And here is our RPAD and LPAD query

SELECT ID
,RIGHT( '00000' + CONVERT(VARCHAR(5), Id), 5) as LPAD_Example
,LEFT( CONVERT (VARCHAR(5), Id)+'00000' , 5) as RAPD_Example
FROM Temp

--END--

- Mangal Pardeshi