Left Padding in SQL Server – 3 LPAD() Equivalents

If you use Oracle Database or MySQL, you’re lucky enough to have the LPAD() and RPAD() functions, which allow you to pad a string with a given character (or characters) to its left and/or right.

However, SQL Server (or more precisely, T-SQL), doesn’t include these functions. So if you need some left padding, you’ll need to improvise.

This article presents four options for padding a number with leading zeros in SQL Server. So you can do stuff like turn 7 into 007. Three of these options work on strings, so you can also apply padding to textual data.

Method 1 – Use the FORMAT() Function

If you need to apply leading zeros to a number, then this option should be all you need. This option uses the FORMAT() function. This function returns the number as a string in our specified format:

SELECT FORMAT(7, '000');

Result:

In this case, we use the 0 format specifier to format the number with leading zeros where applicable. This is just one of many custom format specifiers. Custom format specifiers allow us to be very precise about how our number is formatted.

Note that the FORMAT() function is only for numbers and datetime values. So if you need to apply padding to an actual string, read on.

Method 2 – Use the RIGHT() Function

The second method uses the RIGHT() function to return only the rightmost part of the string, after adding some leading zeros.

Although I’m applying leading zeros to a “number” here, it’s actually a string representation of a number. So if you need to apply padding to a string instead of a number, then this method should work.

SELECT RIGHT('000' + '1234', 7);

Result:

The 7 specifies how many characters the end result should be (after the zeros have been added).

Reducing the Length

So if we reduce that number, it will reduce the number of leading zeros:

SELECT RIGHT('000' + '1234', 6);

Result:

Increasing the Length

But if we increase the number, we need to make sure that we’ve specified enough zeros to make up the required length:

SELECT RIGHT('000000' + '1234', 10);

Result:

0000001234

Otherwise we end up with this:

SELECT RIGHT('000' + '1234', 10);

Result:

Cutting the Number Short

Also note that if you don’t specify enough characters for the resulting string length, the number will be cut off, and you’ll only get the rightmost part of the number:

SELECT RIGHT('000' + '1234', 2);

Result:

So in this case, the initial number was truncated plus the leading zeros were ignored.

This is one scenario where the outcome differs to that of MySQL and Oracle’s LPAD() function. That function would’ve produced the first 2 (unpadded) digits instead of the last 2 digits. Like this:

SELECT LPAD(1234, 2, 0);

Result:

If you need a SQL Server solution that will behave like LPAD() in such cases, try this:

SELECT RIGHT(REPLICATE('0', 3) + LEFT('1234', 2), 2);

Result:

Although, bear in mind that you get the same result simply using the LEFT() function by itself:

SELECT LEFT('1234', 2);

Result:

Plus I’m struggling to think of a reason why anyone want to cut the number short (considering they’re trying to pad it), but at least this is something to consider.

Method 3 – Use a Combination of RIGHT() and REPLICATE()

This method is almost the same as the previous method, with the only difference being that I simply replace the three zeros with the REPLICATE() function:

SELECT RIGHT(REPLICATE('0', 3) + '1234', 7);

Result:

The REPLICATE() function saves you from having to type out each zero (or other character) multiple times.

Bonus Method: Method 4 – Use a Combination of REPLACE() and STR()

This method comes from a completely different angle to the previous methods:

SELECT REPLACE(STR('1234', 6),' ','0');

Result:

Here we use the REPLACE() function in conjunction with the STR() function to convert a number to a string of a specific length, then convert any space characters to a zero.

One thing to be careful of is that, if you cut the number short (like we did in a previous example), you’ll end up with a bunch of asterisks instead of the (shortened) number:

SELECT REPLACE(STR('1234', 2),' ','0');

Result:

**