When working with SQL Server, there may be situations where you need to convert an integer to a string value. In this tutorial, we will explore different methods to achieve this using T-SQL scripts and stored procedures.

Problem

Let’s say we have a column named OrderQty in our WorkOrder table, which is numeric in nature. If we try to concatenate this numeric value with a string in a SELECT statement, we will encounter an error.

SELECT 'Order quantity:' + space(1) + [OrderQty]
FROM [Production].[WorkOrder]

The above code will generate the following error message: “Conversion failed when converting the varchar value ‘Order quantity: ‘ to data type int.”

So, in order to concatenate a string with a number, we need to convert the numeric value into a string. Let’s explore different methods to achieve this.

Solution

Using CAST Function

The CAST function allows us to convert an integer to a string. Here’s an example:

SELECT 'Order quantity:' + space(1) + CAST(OrderQty as varchar(20)) as Ordqty
FROM [Production].[WorkOrder]

The above code will return the converted integer values as strings.

Using CONVERT Function

The CONVERT function is similar to CAST, but with a slightly different syntax. Here’s an example:

SELECT 'Order quantity:' + space(1) + CONVERT(varchar(20), OrderQty) as Ordqty
FROM [Production].[WorkOrder]

The above code will also convert the integer values to strings.

Using CONCAT Function

The CONCAT function can be used to implicitly convert values into strings when concatenating different data types. Here’s an example:

SELECT CONCAT('Order quantity:', space(1), OrderQty) as Ordqty
FROM [Production].[WorkOrder]

Note that if you need to convert an integer to a string for comparison purposes, it is recommended to use the CAST or CONVERT functions.

Converting Integer to String in a Table

If you need to convert the data type of a column from an integer to a string in a table, you can do so using either the GUI or T-SQL.

Using GUI

In SQL Server Management Studio (SSMS), you can use the Design option to change the data type of a column. Simply right-click on the table name, go to Design, and change the data type of the desired column. However, be aware that this may require the table to be dropped and re-created, which can result in data loss.

Using T-SQL

If you prefer using T-SQL, you can use the ALTER TABLE statement to change the data type of a column. Here’s an example:

ALTER TABLE dbo.workorder
ALTER COLUMN orderqty varchar(50)

The above code will change the data type of the “orderqty” column to varchar(50).

To change the data type back to integer, you can use the following code:

ALTER TABLE dbo.workorder
ALTER COLUMN orderqty int

Converting Integer to String in WHERE Clause

If you need to compare a string with a numeric value in a WHERE clause, you can do so by converting the integer to a string. Here’s an example:

SELECT TOP 5 *
FROM dbo.workorder
WHERE OrderQty = '8'

The above code will return the records where the OrderQty is equal to the string value ‘8’.

Converting Integer to String in JOIN Tables

When joining tables where one has a string value and the other has an integer value, you don’t need to explicitly convert the data types. SQL Server will implicitly convert and compare the values. Here’s an example:

SELECT TOP 50 wo.Orderqty, wo.Productid, pwo.Stockedqty
FROM dbo.workorder wo
INNER JOIN Production.WorkOrder pwo ON wo.OrderQty = pwo.OrderQty

The above code will join the “dbo.workorder” table with the “Production.WorkOrder” table based on the OrderQty column, which has different data types.

By using the appropriate conversion methods, you can effectively convert integers to strings in SQL Server and perform various operations based on your requirements.

That’s it! You now have a better understanding of how to convert integers to strings in SQL Server. Happy coding!

Article Last Updated: 2022-06-15