Convert Int to Fixed Length String in SQL Server

by

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.

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, you have data in numeric form, and you will need to convert the data to string with fixed length to maintain the data consistency.

Sometimes, you need to show the data as a string in a user interface. Maybe you have to align the data in tables for that first; you need to make a fixed length of string.

If you want to sort or compare the data, then a fixed length of string 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 that you can use in combination 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 here, you will use the CONVERT() and RIGHT() functions together to convert int to 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 length of 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 have to convert the order number column to a string with a fixed length of six characters. 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 execution of 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 VARCAHR (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() that 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 you have used the RIGHT() and CONVERT() functions.

Here, you need to understand only that the CAST() function is doing 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

In this SQL Server tutorial, you learned how to convert an integer to a fixed-length string in SQL Server. You learned about the functions CAST(), CONVERT(), and RIGHT(), which help in converting the integer to a fixed-length string.

You may also like: