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 :
- Input String – The unaltered string to add to.
- Desired Length – The final length of the string after adding the character(s).
- 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 SUBSTRING
function .
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 !