SQL LPAD and RPAD Functions Alternative in Synapse and TSQL

Many relational databases such as Oracle, Teradata supports LPAD and RPAD functions. These functions are quite useful when you are formatting data before storing it in a database or displaying data on reports. For example, add zero to integer column. But, Azure Synapse data warehouse which is based on the Microsoft SQL Server TSQL does not support LPAD and RPAD functions. You have to use TSQL built-in functions or create your own user defined functions (UDF). In this article, we will check what are the LPAD and RPAD Functions Alternative in Synapse and TSQL.

LPAD and RPAD Functions Alternative in Synapse and TSQL

The LPAD and RPAD functions return a string left-padded and right-padded with specified characters to a certain length respectively.

LPAD Function Alternative in Synapse and TSQL

The LPAD function return a string left-padded with specified characters to a certain length.

The following Teradata example pads a string with the character (*) to a length of 5:

SELECT LPAD( 'ABC', 5, '*' )
;

LPAD('ABC',5,'*')
-----------------
**ABC

In this example, the source string ‘ABC‘ has length 3, therefore, only two more characters padded to make the length of the result string 5.

Consider following example

SELECT LPAD( 'ABCDEF', 5, '*' );
LPAD('ABCDEF',5,'*')
--------------------
ABCDE

In this example, the length of the source string ‘ABCDEF‘ is 6. However, the target length is 5, therefore, the LPAD function trims 1 right most character from the source string.

Following list is an alternate approach that you can use.

Use TSQL built-in functions as LPAD Alternative
LPAD User Defined Functions

Now, let us check these two approach in brief.

Use TSQL built-in functions – RIGHT and REPLICATE as a LPAD Alternative

You can use RIGHT and REPLICATE built-in function to append specified character to a certain length.

The following example left-pads a string with the character (*) to a length of 5.

Select right(replicate('*',5) + 'ABC',5) as LPAD;
LPAD
----
**ABC

LPAD User Defined Function in TSQL

Alternatively, you can create LPAD user defined function in an Azure Synapse data warehouse or MSSQL TSQL.

Following is the lpad user defined function.

CREATE FUNCTION [dbo].[LPAD] (@input [varchar](8000),@number [int],@sequence [varchar](8000)) 
RETURNS varchar(8000)
AS
BEGIN
     DECLARE
         @inputLength int = null,
         @dataDiff int = null,
         @output varchar(8000) =  null;
     --length of input
     SET @inputLength = DATALENGTH(@input);
      IF @inputLength < @number
	     BEGIN
		 --difference between input string and output length 
         SET @dataDiff = @number - @inputLength;
		 --replicate the input with input sequence to match output length
         SET @output = REPLICATE(@sequence, @dataDiff) + @input;
		 END
	   ELSE
		   BEGIN
		    -- Trim if length is more then target
			SET @output = LEFT(@input,@number)
		   END
 RETURN @output ;
END
GO

And following is the output.

SELECT dbo.LPAD( 'ABC', 5, '*' ) as LPAD;
LPAD
----
**ABC

SELECT dbo.LPAD( 'ABCDEF', 5, '*' ) as LPAD;
LPAD
----
ABCDE

Select left('ABC' + replicate('*',5),5) as RPAD;"
RPAD
----
ABC**

RPAD Function Alternative in Synapse and TSQL

The RPAD function return a string right-padded with specified characters to a certain length.

The following Teradata example pads a string with the character (*) to a length of 5:

SELECT RPAD( 'ABC', 5, '*' );
RPAD('ABC',5,'*')
-----------------
ABC**

In this example, the source string ‘ABC‘ has length 3, therefore, only two more characters right-padded to make the length of the result string 5.

Consider following example

SELECT RPAD( 'ABCDEF', 5, '*' );
RPAD('ABCDEF',5,'*')
--------------------
ABCDE

In this example, the length of the source string ‘ABCDEF‘ is 6. However, the target length is 5, therefore, the RPAD function trims 1 right most character from the source string.

Following list is an alternate approach that you can use.

Use TSQL built-in functions as RPAD Alternative
RPAD User Defined Functions

Now, let us check these two approach in brief.

Use TSQL built-in functions – LEFT and REPLICATE as a RPAD Alternative

You can use LEFT and REPLICATE built-in function to append specified character to a certain length.

The following example right-pads a string with the character (*) to a length of 5.

Select left('ABC' + replicate('*',5),5) as RPAD;"
RPAD
----
ABC**

RPAD User Defined Function in TSQL

Alternatively, you can create RPAD user defined function in an Azure Synapse data warehouse or MSSQL TSQL.

Following is the rpad user defined function.

CREATE FUNCTION [dbo].[RPAD] (@input [varchar](8000),@number [int],@sequence [varchar](8000)) 
RETURNS varchar(8000)
AS
BEGIN
     DECLARE
         @inputLength int = null,
         @dataDiff int = null,
         @output varchar(8000) =  null;
       -- length of input
     SET @inputLength = DATALENGTH(@input);
      IF @inputLength < @number
	     BEGIN
	     --difference between input string and output length 
         SET @dataDiff = @number - @inputLength;
		 --replicate the input with input sequence to match output length
         SET @output = @input + REPLICATE(@sequence, @dataDiff) ;
		 END
	   ELSE
		   BEGIN
		    -- Trim if length is more then target
			SET @output = LEFT(@input,@number)
		   END
 RETURN @output ;
END
GO

And following is the output.

SELECT dbo.RPAD( 'ABC', 5, '*' ) as RPAD;
RPAD
----
ABC**

SELECT dbo.LPAD( 'ABCDEF', 5, '*' ) as RPAD;
RPAD
----
ABCDE