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.
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.