#SQLSERVERLearning : String Padding in Sql Server (LPAD/RPAD)

All my blogs with #SQLSERVERLearning is intended to cover all stuff identified as difference between Oracle and SQL Server.
Hopefully it might be helpful for Oracle guy also working in SQL Server world.
All finding or Observations with SQL Server for any Oracle guy can be access here.

Oracle provide LPAD and RPAD function for padding a specific characters as per specified length.
SQL Server directly don’t provide any in-build function to achieve similar functionality.

We will try to understand, on how similar functionality can be implemented in SQL Server.
Using standard function SQL server provide we will try to implement LPAD and RPAD functionality.

1. LEFT and RIGHT
Return varchar from left or right up to length specified.

SELECT LEFT('abcdefg',1) , RIGHT('abcdefg',1);

LEFT RIGHT
---- -----
a         g

2. REPLICATE
Return replication of characters based on number of times specified.

SELECT REPLICATE('AB', 4) "REPLICATE"

REPLICATE
---------
ABABABAB

We will try to recreate Oracle LPAD and RPAD function using UDF in SQL Server.

--Creating LPAD Function
CREATE FUNCTION LPAD(@STR VARCHAR, @PADD_LEN INT , @PAD_STR VARCHAR = ' ')
RETURNS VARCHAR(max)
AS
BEGIN
RETURN RIGHT( REPLICATE(@PAD_STR, @PADD_LEN) + @STR, @PADD_LEN);
END;

--Creating RPAD Function
CREATE FUNCTION RPAD(@STR VARCHAR, @PADD_LEN INT , @PAD_STR VARCHAR = ' ')
RETURNS VARCHAR(max)
AS
BEGIN
RETURN LEFT(@STR + REPLICATE(@PAD_STR, @PADD_LEN) , @PADD_LEN);
END;

Example from newly created function,

SELECT DBO.LPAD('A',2,'_') "LPAD" , DBO.RPAD('A',2,'_') "RPAD"

LPAD RPAD
----- -----
_A A_

We can implement same in SQL Server to get similar LPAD and RPAD functionality as in Oracle.

About Deepak Mahto

Having 8+ Years of relevant/exciting/fruitful/challenging/learning in Oracle Technology. Currently working as Oracle Performance Consultant in an MNC. Hold Expertise in Performance Engineering, SQL Tuning, Database Tuning, SQL, PLSQL Development/Design Considerations and Oracle Internals. I enjoy reading on Oracle internals, posting some of my finding or observations and playing football.
This entry was posted in Helper SQL, SQL Server Learning and tagged , , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s