Convert Integers to Strings in MySQL

In MySQL, you may sometimes need to convert integer values to strings, especially when you want to concatenate numbers with text or display numbers in a formatted output.

MySQL provides functions such as CAST() and CONVERT() to convert integers into strings, making it easier to perform these tasks.

In this tutorial, we will cover how to convert integers to strings and demonstrate the usage of methods CAST() and CONVERT() for converting integers to strings in MySQL, with practical examples.

Methods to Convert Integers to Strings in MySQL

In MySQL, converting integers to strings can be achieved using the CAST() and CONVERT() functions. These functions change the data type of integer values to a string type like CHAR or VARCHAR.

Using CAST() to Convert Integers to Strings

The CAST() function in MySQL allows you to convert an integer to a specific data type. To convert an integer to a string, use CAST(integer AS CHAR) or CAST(integer AS VARCHAR).

</>
Copy
CAST(integer AS CHAR);
CAST(integer AS VARCHAR);

Using CONVERT() to Convert Integers to Strings

Alternatively, you can use the CONVERT() function to convert integers to strings by specifying the target data type:

</>
Copy
CONVERT(integer, CHAR);
CONVERT(integer, VARCHAR);

Setting Up a Sample Table

To demonstrate these conversions, we’ll create a students table containing integer values for student IDs and grades.

1. Create a new database called school (if it doesn’t exist):

</>
Copy
CREATE DATABASE IF NOT EXISTS school;

2. Use the school database:

</>
Copy
USE school;

3. Create a students table with student_id and grade columns:

</>
Copy
CREATE TABLE students (
    student_id INT PRIMARY KEY AUTO_INCREMENT,
    grade INT
);

4. Insert sample data into the students table:

</>
Copy
INSERT INTO students (grade)
VALUES (85), (90), (78), (92), (88);
Convert Integers to Strings in MySQL - Setup for Examples

With this setup, we can experiment with converting integers to strings in the students table.

Examples of Converting Integers to Strings

Now that the table is ready, let’s look at examples of converting the integer student_id and grade columns to string format.

students table data

1. Using CAST() to Convert Grade to String

To convert the grade column from an integer to a string using CAST():

</>
Copy
SELECT grade, CAST(grade AS CHAR) AS grade_string
FROM students;

This query converts each grade from an integer to a string, allowing it to be treated as text data.

Using CAST() to Convert Integer to String in MySQL

2. Using CONCAT() to Concatenate Text and Integer Values

To add a descriptive text alongside the integer grade, CONCAT() can be used in combination with CAST() or CONVERT():

</>
Copy
SELECT student_id, CONCAT('Grade: ', CAST(grade AS CHAR)) AS grade_description
FROM students;

This query concatenates “Grade: ” with each grade value, converting it to a string for display purposes.

Using CONCAT() to Concatenate Text and Integer Values in MySQL