How To Convert Int to Fixed Length String in SQL Server

As a database developer, I worked in SQL Query, and I was required to convert integers into fixed-length strings in SQL Server.

After multiple research studies, I found a simple and standard way of converting. In this SQL Server tutorial, you will learn how to convert int to fixed length string in SQL Server.

You will understand how to use the CONVERT(), RIGHT(), and CAST() functions together to convert the given integer value to a fixed-length string.

Table of Contents

Convert Int to Fixed Length String in SQL Server

Sometimes, to present the data, you need to convert from one data type to another. For example, if you have data in numeric form, you must convert it to a string with a fixed length to maintain consistency.

Sometimes, you must show the data as a string in a user interface. For that, you might have to align the data in tables first and make a fixed-length string.

If you want to sort or compare the data, a string with a fixed length will give expected results instead of numeric values without fixed lengths.

There can be more reasons behind converting int to fixed length string in SQL Server. So here you will understand the different methods you can use to convert int to fixed length string in SQL Server.

Convert Int to Fixed Length String in SQL Server

There are two functions CONVERT(), which converts the data type of a given value to another data type, and RIGHT(), which extracts the rightmost characters from the given value in SQL Server.

So, in this case, you will use the CONVERT() and RIGHT() functions together to convert an int to a fixed-length string in SQL Server.

Now, suppose you have an Orders table with columns order_id and order_number, and you have a task where you need to convert the value of the order_number column to a fixed-length string. The string should be 6 characters long.

The table is shown below.

Convert Int to Fixed Length String in SQL Server Table Orders

As you can see in the above order_number column of the Orders table that contains the order number, but the order number is not in fixed length such as 123, then the order number with 4567, and the same for others.

Now, you must convert the order number column to a string with a six-character fixed length. For that, use the below query.

SELECT RIGHT('000000'+CONVERT(VARCHAR(5), order_number),6) AS FixedLength_String
FROM Orders;

Convert Int to Fixed Length String in SQL Server

After executing the above query, you can see that each value of the order_number column has a fixed length of six characters. For example, count the number of characters in each order number; you will get a total of 6 characters.

Look at the query part RIGHT(‘000000’+CONVERT(VARCHAR(5), order_number),6), the CONVERT() function converts the value of order_number column into VARCHAR (string).
After that, RIGHT(‘000000’ + …) means concatenating the 6 zeros with the converted string. Then RIGHT(…+ …, 6) means extract the rightmost 6 characters from the concatenated string.
Which results in a 6-character long string that represents the order number.

You can also use the CAST(), which converts the data type of the given value to another data type. So, let’s see the same example with the RIGHT() and CAST() functions together.

SELECT RIGHT('000000'+ CAST(order_number AS VARCHAR(6)),6) AS FixedLength_String
FROM Orders;

Convert Int to Fixed Length String in SQL Server Using Cast() Function

Again, this time, you get the same result as using the RIGHT() and CONVERT() functions.

Here, you only need to understand that the CAST() function is performing the task of the CONVERT() function.

This is how to convert int to a fixed-length string in an SQL server using the methods.

Conclusion

Now, you have an idea of how to convert an integer to a fixed-length string in an SQL Server. Additionally, you learned about the functions CAST(), CONVERT(), and RIGHT(), which help convert the integer to a fixed-length string.