Padding in SQL Server 2008

In this article I will explain how to use the padding in table column.
  • 12701

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:

Left_Padding.jpg

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:

Left_Padding-in-TableColumn.jpg

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:

Right_Padding.jpg


Categories

More Articles

© 2019 DotNetHeaven. All rights reserved.