Padding in SQL Server 2008
In this article I will explain how to use the padding in table column.
Introduction
LPAD and RPAD are the functions which formats the string by adding trailing or leading zeros in string. But SQL Server 2008 does not offer functions like LPAD and RPAD. Oracle has these two formatting functions. So you can achieve left and right padding by using other string functions available in SQL Server 2008. So you could do it in this way.
Example
Lets take a practical example. Suppose you have table named "Employee" having following schema.
1. Left Padding
Suppose you have a numeric column "salary" and you want to return it as a string with exactly 9 characters. This can be implemented by doing left padding with 0's shown below.
select top 10 LEFT (REPLICATE('0',5)+cast(Salary as varchar(10)),10)Padded_Salary from Employee
|
OUTPUT:
Replicate function is used to replicate 0 to 5 times and CAST function is used on the assumption that the value you padding is numeric. If values are already in string format you could do it like this:
SELECT REPLICATE ('X', (10 - LEN (F_Name))) + F_Name AS Padded_F_Name FROM Employee
|
OUTPUT:
2. Right Padding
For applying trailing zeros, type following code:
Select top 10 LEFT (cast (Salary as varchar(10))+REPLICATE('0',10),10) PaddedSalary from Employee
|
OUTPUT:
