How To Convert Int to Fixed Length String in SQL Server

While creating a function in the SQL query, 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.

Convert Int to Fixed Length String in SQL Server

There are two functions:

• CONVERT() and
• RIGHT()

CONVERT() converts the data type of a given value to another data type, and RIGHT() 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.

Suppose you have an OrderDetail table with columns Order_id, Product_Id, Product_Name, and Deliveredtime. You have to convert the value of the Product_Id column to a fixed-length string, which should be 6 characters long.

The table is shown below.

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

• 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 extracting the rightmost 6 characters from the concatenated string, Resulting in a 6-character long string representing the order number.

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

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

If we are giving 8 characters, then we will get 8 characters in the output. See the below image.

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( Product_Id AS VARCHAR(6)),6) AS FixedLength_String
FROM  OrderDetail;

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.

Also Read: How to Convert DateTime to String in SQL Server

Conclusion

Now, you know 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.

You may also like:

• How to Convert Int to String with Leading Zero in SQL Server?
• Convert Int to String with Commas in SQL Server
• Format Number with Commas in SQL Server