Thursday, February 2, 2012

LPAD or RPAD in Sql Server


Before proceeding we will go through the definition of LPAD and RPAD.
Defintion--:
LPAD(column|expression,n,’string’) --> Pads the character value right-justified to a total width of n character positions.
RPAD(column|expression,n,’string’) ) --> Pads the character value left-justified to a total width of n character positions.

To implement these functions we have no any direct method in sql server but we can implement it with the combination of LEFT or Right and REPLICATE function.

Here's the solution --:
DECLARE @test VARCHAR(3)
SET @test = '143'
SELECT
RIGHT(REPLICATE('*',10) + @test ,10) AS RPAD,
LEFT(@test + REPLICATE('*',10) ,10) AS LPAD 

Your output will be look like this,

1 comment: