SQL Convert INT to String

Problem

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 article, we will look at various ways in SQL Server to convert int to string include the SQL CONVERT function, SQL CAST function and SQL CONCAT function.

Solution

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:

Conversion failed when converting the varchar value 'Order quantity: ' to data type int.

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.

SQL CAST int to string

The following example shows how to use the SQL CAST function for an int to string conversion.

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:

SQL Server 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 Int to String SQL Server

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]

Using GUI

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.

Saving changes is not permitted. The changes that you have made require the following tables to be dropped and re-created. You have either made changes to a table that can't be re-created or enabled the option Prevent saving changes that require the table to be re-created.

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.

Using T-SQL

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

Next Steps

For more information SQL CONVERT functional with Transact-SQL, refer to these links:

Resources

Community

Subscribe