Concatenate INT With VARCHAR in SQL

When it comes to MySQL, it’s important to handle the concatenation of an integer (INT) with a variable character (VARCHAR) with care to ensure optimal results. This is because integers and characters are different datatypes and can’t be concatenated directly. To concatenate numbers and text, we must first convert the integer datatype to a string.

Here, we will discuss about the process of concatenating an INT and VARCHAR in MySQL, along with best practices and examples. We will also discuss common scenarios and challenges that users encounter when combining these data types

Concatenate INT With VARCHAR

Direct concatenation of strings and integers is not possible in SQL. To concatenate strings and numbers we can use the CONVERT() and CAST() function. Let’s understand both these methods in detail below.

Demo SQL Database

Let’s create a demo SQL table, and try concatenating integers and strings.

OrderID	Product
1	Apple
2	Orange
3	Banana

To create this table write the following SQL queries.

MySQL

CREATE TABLE Orders(
  OrderID INT UNIQUE,
  Product VARCHAR(255)
);
INSERT INTO Orders(OrderID, Product) VALUES 
(1, 'Apple'),
(2, 'Orange'),
(3, 'Banana');

Concatenate INT with VARCHAR in SQL Example

Let’s say you have a table named Orders. Table Orders contains two columns OrderID(INT) stores the unique ID of orders and Product(VARCHAR) stores the name of the product. Your task is to select a column OrderDetails that concatenates the OrderID and Product columns.

Before we dive into the conversion process, let’s briefly understand what INT and VARCHAR data types are:

• INT: This data type is used to store integer values. It can hold both positive and negative whole numbers.
• VARCHAR: This stands for variable character and is used to store non-numeric characters or strings. The length of the string can vary, hence the name “variable character”.

Using CONVERT() Function to Concatenate String and Integers

The CONVERT() function in SQL is used to convert a value from one data type to another. It’s particularly useful when you need to manipulate the data type of a column value, variable, or expression.

Syntax:

CONVERT (data_type, expression)

Query:

SELECT OrderID, Product, CONCAT(CONVERT(OrderID, CHAR), '-', Product) AS OrderDetails FROM Orders;

1. SELECT OrderID, Product: This part of the query is selecting the OrderID and Product columns from the Orders table. These will be included in the result set.
2. CONVERT(OrderID, CHAR): This function is converting the OrderID, which is an integer, into a string (CHAR). This is necessary because you can’t directly concatenate strings and integers in SQL.
3. CONCAT(CONVERT(OrderID, CHAR), ‘-‘, Product) AS OrderDetails: This part of the query is concatenating the now-string OrderID, a hyphen (‘-’), and the Product into a single string. This concatenated string is being given the alias OrderDetails. This will appear as a column in the result set.
4. FROM Orders: This specifies that the query should be run on the Orders table.

So, the result of this query will be a table with three columns: OrderID, Product, and OrderDetails. The OrderDetails column will contain a string for each record in the format of “OrderID-Product”.

Output:

Query output

Explanation: The SELECT query retrieves data from the ‘Orders‘ table, including ‘OrderID’ and ‘Product’. The CONCAT() function combines ‘OrderID’ and ‘Product’ with a hyphen, creating a new column named ‘OrderDetails.’ The output displays the original columns along with the concatenated information in the ‘OrderDetails’ column for each order.

Using CAST() Function to Concatenate String and Integers

The CAST() function in SQL is used to convert a value from one data type to another. It’s a part of SQL standards, which means it’s more universally supported across different database systems compared to some other functions.

Syntax:

CAST (expression AS data_type)

Query:

SELECT OrderID, Product, CONCAT(CAST(OrderID AS CHAR), '-', Product) AS OrderDetails FROM Orders;

1. SELECT OrderID, Product: This part of the query is selecting the OrderID and Product columns from the Orders table. These will be included in the result set.
2. CAST(OrderID AS CHAR): This function is converting the OrderID, which is an integer, into a string (CHAR). This is necessary because you can’t directly concatenate strings and integers in SQL.
3. CONCAT(CAST(OrderID AS CHAR), ‘-‘, Product) AS OrderDetails: This part of the query is concatenating the now-string OrderID, a hyphen (‘-’), and the Product into a single string. This concatenated string is being given the alias OrderDetails. This will appear as a column in the result set.
4. FROM Orders: This specifies that the query should be run on the Orders table.

So, the result of this query will be a table with three columns: OrderID, Product, and OrderDetails. The OrderDetails column will contain a string for each record in the format of “OrderID-Product”.

Output:

query output

Explanation: The SELECT query fetches data from the ‘Orders’ table, including ‘OrderID’ and ‘Product’. The CONCAT() function combines ‘OrderID,’ explicitly converted to CHAR using CAST(), and ‘Product’ with a hyphen. The output presents the original columns along with the concatenated information in the ‘OrderDetails’ column for each order.

Conclusion

Both the CONVERT() and CAST() functions in SQL can be used to concatenate an INT and a VARCHAR data type. These functions convert the INT to a CHAR (which is a string data type), allowing concatenation of numbers and texts.

This technique is useful when you need to manipulate the data type of a column value, variable, or expression in SQL. Remember, direct concatenation of strings and integers is not possible in SQL, hence the need for conversion.