SQL Server Convert Int to String Padding

by

In this SQL Server tutorial, you will learn about SQL Server convert int to string padding where you will introduced to the concept of padding.

After that, you will understand the LEFT(), RIGHT(), CONVERT(), and REPLICATE() functions that you can use these functions in combination to apply the left and right padding to the string.

SQL Server Convert Int to String Padding

The padding means adding characters such as zeros or spaces to the beginning or end of any string to which you want to be in a specific length. When you need to present the data in a specific way or display it in a meaningful way, you use padding.

You can perform padding in two ways, left padding and right padding. For example, you have a number 345, and you want to add the zeros at the beginning of this number. Then you will add the zeros like this 00345. Here you left padded the number 345 with two zeros.

Now if you have a string such as ‘SQL’ and you want to add the spaces at the end of the word ‘SQL’ like this ‘SQL ‘ and now you right padded the string ‘SQL’ with spaces and this is called right padding.

While padding instead of zeros or spaces, you can use any characters based on your requirements.

Now you know what padding means. Let’s move and learn how to convert int to string padding.

SQL Server Convert Int to String Padding: Left Padding

As you know left padding means adding the characters at the of the string or numbers. So here you will use the RIGHT() and CONVERT() functions in combination to convert the given integer to a string with left padding.

For example, here you will use the CustomerOrders table with columns OrderID, CustomerName, and OrderDate and the table is shown below.

SQL Server Convert Int to String Padding Left Padding Table CustomerOrders

By looking at the above table, now you have a task where you have to make the OrderID more readable by leading zeros, so here you need to add the zeros at the beginning of each order ID.

Also, make sure that the order ID should be six characters long and convert the order ID from integer to string.

For that use the below query.

SELECT OrderID, RIGHT('0000' + CONVERT(VARCHAR, OrderID),6) LeftPaddedOrderId
FROM CustomerOrders;
SQL Server Convert Int to String Padding Left Padding

As you can see in the above output, all the value of the OrderID column is converted to a string with leading zeros which is six characters long. For example, the order ID 000011 has six characters and contains 4 zeros at the beginning, and the same for the other values.

After this let’s understand what is happening with the query when you execute that. So in this part RIGHT(‘0000’ + CONVERT(VARCHAR, OrderID),6).

  • First, convert all the values of the OrderID column from int to VARCHAR (string) data type using the CONVERT() function. Then the five zeros are added to each string of the OrderId column using (‘0000’ + ..).
  • Finally, the RIGHT(…,6), means extracting the rightmost six characters from the string with zeros.
  • After that, the result is the padded string that if the value within the OrderID column is less than six characters in length, then that value is padded with zeros, otherwise if the value of the OrderID column is already six characters in length, it remains unchanged.

This is how to convert the int to string and apply the left padding to that string with zeros or any other characters in sql server.

SQL Server Convert Int to String Padding: Right Padding

After learning the left padding, you will understand how to convert int to string with the right padding. The right padding means adding the character at the end of the given number or string.

For that here you will use the LEFT(), REPLICATE(), and CONVERT() functions in combination to convert the integer to a string with the right padding.

Now here you will use the same CustomerOrders table and this time you have a task where you have to add the spaces to the end of the value within the OrderID column to make sure that each name should be 10 characters long.

For that use the below query.

SELECT 
OrderID,
LEFT(CONVERT(VARCHAR, OrderID) + REPLICATE(' ', 10), 10) AS RightPaddedOrderID
FROM CustomerOrders;
SQL Server Convert Int to String Padding Right Padding

As you can see in the above output, the value of the OrderID column is converted from integer to string and then added the spaces to the end of each string that you can see in the above output.

Now, let’s know how the query is working. Here is the query part LEFT(CONVERT(VARCHAR, OrderID) + REPLICATE(‘ ‘, 10), 10) convert the integer to string and apply the right padding to each string.

As you know the CONVERT() function converts the value of the OrderID column to VARCHAR (string). Then the REPLICATE(‘ ‘,10) generates a string based on the specified character which is space in this case. So here it generates a string of 10 spaces.

Finally, the function LEFT(…., 10) extracts the leftmost 10 characters from that string which contains the spaces.

The result is a string with trailing spaces or right padded string with spaces. This is how you can convert int to string with the right padding in SQL Server.

I hope that you understand how to convert the given integer to a string and apply padding based on your requirement to display the data in a readable way in sql server.

Conclusion

In this SQL Server tutorial, you learned how to convert an int to a string with padding in SQL Server. Where you learned about the functions such as RIGHT(), CONVERT(), LEFT(), and REPLICATE() to apply padding to the string.

You may also like: