Convert INT to VARCHAR SQL

In SQL, there are situations when we need to alter the way numbers are displayed in databases. Often, We come across situations where we must convert data from one type to another to suit specific requirements or formatting needs. One common transformation task is converting integer (INT) values to strings (VARCHAR) in SQL.

INT: An acronym for “integer.”It is a numeric data type used to store whole numbers. It usually takes up 4 bytes of storage and, depending on whether it’s signed or unsigned, can represent a large range of integer values, from -2,147,483,648 to 2,147,483,647 (signed) or 0 to 4,294,967,295 (unsigned).

VARCHAR: An acronym that represents “variable-length character string.” It is a character data type for storing variable-length alphanumeric data. The length of a VARCHAR column can vary up to a maximum specified length. It is more flexible in terms of storage space, as it only occupies storage space equal to the actual length of the data stored, plus two bytes for overhead.

Convert INT to VARCHAR SQL

In SQL, there are several ways to convert an INT to VARCHAR

1. Using the CAST Function

Syntax:

SELECT CAST(int_column AS VARCHAR(10)) AS varchar_column

FROM your_table;

• The int_column is an integer that is converted to a VARCHAR datatype using the CAST function.
• The length of the generated VARCHAR column is specified in (10). You can change this figure according to the length specifications for your data.
• AS varchar_column assigns a name to the converted column in the result set.

Example of CAST Function

Assume we have a table called employees with an INT type employee_id column:

employee_id
1
2
3

we want to convert it into a varchar

SELECT 
    CAST(employee_id AS VARCHAR(10)) AS employee_id_as_varchar
FROM 
    employees;

Output:

employee_id_as_varchar
1
2
3

• The employee_id column is converted to VARCHAR using the CAST method.
• The resulting VARCHAR column’s maximum length of 10 characters is specified by AS VARCHAR(10).
• An alias for the converted column in the query result is employee_id_as_varchar.

employee_id column originally of type INT, is now a VARCHAR

2. Using CONVERT function

Syntax:

SELECT CONVERT(VARCHAR(10), int_column) AS varchar_column

FROM your_table;

• The int_column can be changed into a VARCHAR datatype using the CONVERT function, which works similarly to CAST.
• The length of the resulting VARCHAR column is specified by (VARCHAR(10)).
• AS varchar_column assigns a name to the converted column in the result set.

Example of CONVERT Function

Assume that we have a table called students and that it contains a column called student_id of type INT.

student_id
1001
1002
1003

SELECT 
    CONVERT(VARCHAR(10), student_id) AS student_id_as_varchar
FROM 
    students;

Output:

student_id_as_varchar
1001
1002
1003

• The student_id column is converted to VARCHAR using the CONVERT function.
• (VARCHAR(10)) indicates that a maximum of 10 characters may be included in the resulting VARCHAR column.
• An alias for the converted column in the query result is student_id_as_varchar.

student_id column, originally of type INT, is now a VARCHAR

3. Using CONCAT Function

Syntax:

SELECT CONCAT(int_column, ”) AS varchar_column

FROM your_table;

• The CONCAT function concatenates the int_column with an empty string (”), effectively converting it to a string.
• The result is stored in a column named varchar_column.

Example

Assume that we have a table called products and that it contains a column called product_id of type INT.

product_id
101
102
103

SELECT 
    CONCAT('Product ID: ', product_id) AS product_id_as_varchar
FROM 
    products;

Output:

product_id_as_varchar
Product ID: 101
Product ID: 102
Product ID: 103

• The string ‘Product ID:‘ is concatenated with the product_id column using the CONCAT function.
• Concatenating the string with the originally-typed INT product_id column immediately converts it to VARCHAR.
• An alias for the concatenated column in the query result is product_id_as_varchar.

product_id column, originally of type INT, is now a VARCHAR

4. Using CAST with CONCAT Function

Syntax:

SELECT

CONCAT(‘prefix_text’, CAST(int_column AS VARCHAR(max_length))) AS alias_name

FROM

your_table;

Assume that we have a table called employees and that it contains a column called employee_id of type INT

employee_id
10
11
12

SELECT 
    CONCAT('Employee ID: ', CAST(employee_id AS VARCHAR(10))) AS employee_id_as_varchar
FROM 
    employees;

Output:

employee_id_as_varchar
Employee ID: 10
Employee ID: 11
Employee ID: 12

• The employee_id column is converted to VARCHAR using the CAST method. The AS VARCHAR(10) indicates that a maximum of 10 characters may be included in the resulting VARCHAR column.
• We then use the CONCAT function to concatenate the string ‘Employee ID: ‘ with the converted employee_id column.
• An alias for the concatenated column in the query result is employee_id_as_varchar.

employee_id column, originally of type INT, is now a VARCHAR

5. Using FORMAT Function

Syntax:

SELECT FORMAT(int_column, ‘0’) AS formatted_varchar_column

FROM your_table;

In SQL Server, the FORMAT function is mostly used to format dates, integers, and currency values.

Although it can be used to convert an INT to VARCHAR, it might not always produce the desired outcomes, particularly when dealing with big numbers or performance-related issues.

Conclusion

One of the basic SQL operations used to convert numerical data into string format is the conversion of INT to VARCHAR.  we have come across a number of way, including CAST, CONVERT, CONCAT, and FORMAT, all of which provide different ways to accomplish the conversion.While CAST and CONVERT provide straightforward conversion mechanisms, CONCAT allows for concatenation with additional text.However, it’s important to think about possible performance effects, particularly when working with big datasets.