Different ways to Convert a SQL INT Value into a String Value
I need to convert an integer to a string value, what options are available in Microsoft SQL Server with T-SQL scripts and stored procedures?
In this tutorial, we will show different ways to convert data in SQL Server from int into a string with various SQL statements.
Let's say that we have the OrderQty column that is numeric in our WorkOrder table in a production SQL database.
SELECT [OrderQty] FROM [Production].[WorkOrder]
When we try to concatenate characters or a string with a number in a SELECT statement, we will receive an error message.
Let's take a look at an example.
SELECT 'Order quantity:' + space(1) + [OrderQty] FROM [Production].[WorkOrder]
The code will generate the following error message:
So, by default, we need to convert OrderQty which is numeric into a string to be able to concatenate the string with the number. To do that, we will try different methods.
CAST Function to convert int to string
The following example shows how to use the CAST function.
In this example, we are converting the OrderQty which is an integer into varchar with SELECT CAST syntax.
SELECT 'Order quantity:' + space(1) + CAST(OrderQty as varchar(20)) as Ordqty FROM [Production].[WorkOrder]
The code returns the following results:
CONVERT function to convert int to string
CONVERT is similar to CAST, the SELECT CONVERT syntax is a little different, but the result is the same.
SELECT 'Order quantity:' + space(1) + CONVERT(varchar(20), OrderQty) as Ordqty FROM [Production].[WorkOrder]
CONCAT function to join different data types
Another function to convert implicitly when we concatenate values is the CONCAT function.
This example shows how to concatenate string values with int values. The CONCAT implicitly converts values into strings. If you need to concatenate different data types, CONCAT is the best option instead of using the + character.
SELECT CONCAT('Order quantity:', space(1), OrderQty) as Ordqty FROM [Production].[WorkOrder]
Note: If you need to convert int into a string to compare, the cast and convert should be used.
Convert int to string in a table
For this example, we use the following SQL query to create a table named dbo.workorder based on the Production.WorkOrder table from the Adventureworks database.
SELECT * INTO dbo.workorder FROM [Production].[WorkOrder]
Then we can use the GUI to change the data type of the table from an int into a string. In SSMS, we can use the Design option to change the data type, by right clicking on the table name.
In the designer, change the data type of OrderQty from an int into nvarchar(50) and save the changes.
You may receive the following error message.
To fix this problem, go to Tools > Options and the go to Designers > Table and Database Designers and uncheck the "Prevent saving changes that require table re-creation" and click OK to save. Note the message below that this will cause the table to be re-created.
If you do not like the UI or SSMS, you can always use the ALTER statement.
The following example shows how to set the column orderqty to the varchar(50) data type.
ALTER TABLE dbo.workorder ALTER COLUMN orderqty varchar(50)
To change the data type to int, the following code could be applied.
ALTER TABLE dbo.workorder ALTER COLUMN orderqty int
Convert int to string in WHERE clause
In this SQL query, we show how to compare a string with a numeric value. OrderQty is numeric and we are comparing it with a string value of 8.
SELECT TOP 5 * FROM dbo.workorder WHERE OrderQty ='8'
Note that the = operator can compare a numeric value with a string.
Convert int to string to JOIN tables
To join two tables where one is a string values and the other is an int values, you do not need to convert the tables to a different data type. T-SQL will convert and compare implicitly. One table is dbo.workorder with the OrderQty of type varchar(50) and the other is Production.WorkOrder with OrderQty of type int.
The following example illustrates how this works.
SELECT TOP 50 wo.Orderqty, wo.Productid, pwo.Stockedqty FROM dbo.workorder wo INNER JOIN Production.WorkOrder pwo ON wo.OrderQty = pwo.OrderQty
For more information SQL CONVERT functional with Transact-SQL, refer to these links:
- Concatenate SQL Server Columns into a String with CONCAT()
- Format numbers in SQL Server
- SQL Server Rounding Functions - Round, Ceiling and Floor
- How to Get Current Date in SQL Server
- SQL Server GETDATE Function
- How SQL Server handles the date format YYYY-MM-DD
- SQL Convert Date to YYYYMMDD
- SQL Server SUBSTRING Examples
- SQL Server SUBSTRING Function
- Build a cheat sheet for SQL Server date and time formats
- SQL Server Data Types (Integer, BigInt, SmallInt, Decimal, DateTime, etc.) Quick Reference Guide
- Learn how to convert strings with SQL CAST and SQL CONVERT
- Transform Data in Power BI with R and Python
- SQL Server LTRIM Function and RTRIM Function
- Compare SQL Server Features - Decimal vs Numeric, Timestamp vs Rowversion, Unique Index vs Unique Constraint
About the author
View all my tips
Article Last Updated: 2022-06-15