Recreating MYSQL’s LPAD Function in SQL Server

If you’ve had to write SQL the chances are that you’ve had to write some queries that utilize some handy platform specific functions. You may use them for so long you even forget that they are platform specific ! Fast forward to a new job and you’re no longer using that same flavor of SQL and suddenly some of your favorite functions are gone.

I’ve encountered a few of these situations during my move from MySQL to SQL Server – ranging from losing the amazing Group_Concat and having to recreate via STUFF ( uggh ) to the much simpler LPAD & RPAD. It can leave you scratching your head and spending an age to recreate the functionality.

Today I decided to just recreate LPAD in SQL Server because it’s something I actually use often enough.

What Is LPAD ?

The LPAD function in MySQL is used to add a string to the left side of the input string.
It can be handy in many cases – the most common case I’ve encountered is padding ‘0’ onto a product id to create a fixed length barcode – e.g. padding ‘1045’ to become ‘000001045’ .

It takes 3 input parameters :

1. Input String – The unaltered string to add to.
2. Desired Length – The final length of the string after adding the character(s).
3. Charachter To Prepend – The character/s to add.

Example:

/*  LPAD( inputString ,  desiredLength , charachterToPrepend )  */
mysql> SELECT LPAD('hi',4,'??');
-> '??hi'
mysql> SELECT LPAD('1045',10,'0');
-> '0000001045'

You can read the official MySQL description here

Requirements

We have just a few requirements as the functionality is pretty simple !

• Accept 3 parameters – the character(s) to prepend, the desired length , and the string to prepend to.
• If the string length is greater than the desired length we do not prepend anything and we need to truncate the input string from the right.
• The function needs to be a Scalar Function type to return a single value.

The Code

Lets break it into bite sized chunks:
These are our input parameters.

 -- This is the string that will be prepended to 
@value NVARCHAR(MAX),
-- This is the desired length of the string after prepending
@length int ,
-- This is the characters that we will prepend
@character NVARCHAR(MAX)

Next we need to declare a few variables that will make things easier to read.

-- Declare our return string 
DECLARE @ReturnValue NVARCHAR(MAX);
-- For clarity get the length of the string 
DECLARE @StrLen int = LEN(@value);
-- For clarity get the target length of the text to prepend 
DECLARE @target int = @length - @StrLen ; 

Ok now we’re getting somewhere!
Next up we need to create an IF / ELSE statement. In it we will check if the input string length is already greater than the desired length for the return string. If the string is greater then we will truncate it, or if it is equal then just return it unaltered.

-- Check if the String is already  equal to the desired length
IF (@StrLen = @length)
    -- Just return the original if so
    SET @ReturnValue =  @value;
ELSE IF (@StrLen > @length)
    SET @ReturnValue =  SUBSTRING(@value, 1, @length);

Next up is the meat of the logic.
We use the REPEAT function to .. you guessed it , repeat a character/s N amount of times ! To figure out N we just divide the @target variable by the length of the @character variable – if the @character is more than one character we’ll need to add + 1 so we don’t accidently get a shorter than needed string, and we’ll remove any unwanted characters using a SUBSTRINGfunction .
Finally concatenate the @value onto this replicated string.

ELSE 
   SET @ReturnValue = SUBSTRING((  replicate(@character,   ((  @target ) / LEN(@character) ) +1  )), 1, @target   )  + @value  

Then just add a return statement and you’re done !

Full Code

CREATE FUNCTION [dbo].[LPAD](
                -- This is the string that will be prepended to 
                @value NVARCHAR(MAX),
                -- This is the desired length of the string after prepending
                @length int ,
                -- This is the characters that we will prepend
                @character NVARCHAR(MAX)

)

RETURNS NVARCHAR(MAX)
AS
BEGIN
    -- Declare our return string 
    DECLARE @ReturnValue NVARCHAR(MAX);
    -- For clarity get the length of the string 
    DECLARE @StrLen int = LEN(@value);
    -- For clarity get the target length of the text to prepend 
    DECLARE @target int = @length - @StrLen ; 

    -- Check if the String is already  equal to the desired length
    IF (@StrLen = @length)
        -- Just return the original if so
        SET @ReturnValue =  @value;
    -- Check if the String is already greater than the desired length , if so then we need to truncate it 
    -- But remember that while LPAD will prepend at the left , it truncates from the right ! 
    ELSE IF (@StrLen > @length)
        SET @ReturnValue =  SUBSTRING(@value, 1, @length);
    ELSE 
        SET @ReturnValue = SUBSTRING((  replicate(@character,   ((  @target ) / LEN(@character) ) +1  )), 1, @target   )  + @value  


    RETURN @ReturnValue

END

Parting Words

As you can see that was pretty easy ! You can easily adapt this into an RPAD function if you need to as well !