How to Correctly Cast an INT to a String in SQL?

Converting Integers to Strings in SQL

Issue

Attempting to directly cast an integer (INT) column to a VARCHAR data type in SQL might fail, depending on the specific database system. Many SQL dialects don't directly support VARCHAR as a target type for this kind of conversion.

Example of an unsuccessful attempt:

SELECT CAST(id AS VARCHAR(50)) AS col1 FROM t9;
SELECT CONVERT(VARCHAR(50), id) AS col1 FROM t9;

Resolution

The solution involves casting the INT to a CHAR data type instead of VARCHAR. This approach is generally more compatible across various SQL databases.

Corrected query:

SELECT CAST(id AS CHAR(50)) AS col1 FROM t9;
SELECT CONVERT(id, CHAR(50)) AS col1 FROM t9;

Note that the CONVERT function syntax was also slightly incorrect in the original example. The correct syntax is either:

CONVERT(expr, type)

or

CONVERT(expr USING transcoding_name)

where expr represents the column or value to be converted, and type specifies the target data type. The USING transcoding_name variant is used for character set conversions.

The above is the detailed content of How to Correctly Cast an INT to a String in SQL?. For more information, please follow other related articles on the PHP Chinese website!