Recreating MYSQL's LPAD Function in SQL Server
Tags: sql, database, mysql
Published: 2022-06-05 14:31:33

> 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: 
```sql 
/*  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](https://dev.mysql.com/doc/refman/8.0/en/string-functions.html#function_lpad)


## 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. 
```sql
 -- 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. 
```sql
-- 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.
```sql
-- 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 `SUBSTRING` function . 
Finally concatenate the `@value` onto this replicated string.  

```sql
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 
```sql
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 ! 

Got any improvements ? Then feel free to comment below !

And if you're feeling generous you can buy me a coffee with the link below ( and yes its all for coffee,  I drink a copious amount of it while writing ☕ )  


[![Buy Me A Coffee](https://cdn.buymeacoffee.com/buttons/default-black.png)](https://www.buymeacoffee.com/SUKaQp9)


Note - This is a [cross-post](https://hashnode.com/post/recreating-mysqls-lpad-function-in-sql-server-cl41ed22n0067pmnvaqev3tu3)