There is no common task in SQL and other programming languages such as type conversion. Type conversion, also known as type casting, plays a crucial role in practical applications as it allows us to convert a value from one data type into another.
One type casting task is converting an integer value, also known as INT, into a string or character data type. This can occur when you need to perform tasks such as string concatenation or displaying to the console.
In this tutorial, we will explore the methods that we can use to convert an INT into a STRING type using SQL. It is good to keep in mind that the methods may vary depending on the database engine. We will be sure to point out where each method is applicable.
SQL INT
In SQL, an INT data type is a numeric type that allows us to store the whole numbers, both positive and negative values. It is good to keep in mind that an INT does not values with decimals. For that, you either need a DECIMAL or a FLOAT value.
An INT is mainly used to represent the values such as counts, IDs, and other whole numbered data values.
SQL String
A STRING, on the other hand, is also known as a character. It is mainly represented as VARCHAR or CHAR in SQL.
A STRING is used to store a sequence of characters or text information. It can hold the alphanumeric characters, symbols, and spaces.
Converting an INT to a STRING allows you to represent the numeric data as a sequence of characters.
Method 1:
One of the most common methods of converting an INT to STRING in SQL is using the CAST or CONVERT() functions. One advantage of using these functions is that they are supported by nearly all database systems including MySQL, PostgreSQL, SQL Server, and Oracle.
The following shows an example usage of these functions to convert an INT to STRING:
SELECT CONVERT(VARCHAR(10), 456);
SELECT 789::VARCHAR;
In the given example, we demonstrate the usage of the CAST and CONVERT functions to explicitly convert an INT into a VARCHAR. We can specify the maximum length of the resulting string as needed.
It is good to keep in mind that the last format is supported by PostgreSQL for quick conversions. The resulting output is as follows:
varchar
---------
789
(1 row)
Method 2:
We can also use the string concatenation to convert an INT to STRING. For example, we can concatenate an empty string, denoted by ” and the target integer value.
This automatically converts the input integer into a string representation. We can concatenate a string using the “||” operator as shown in the following example:
Using the “||” operator as shown in the previous example automatically converts the input value into a string.
Method 3:
Databases such as PostgreSQL and Oracle supports the use of the TO_CHAR() function that allows us to convert a value into various supported data type.
Luckily, it supports the conversion of an INT to a STRING with the specified formatting options. An example is as follows:
The given example shows the example usage of the TO_CHAR() function in PostgreSQL and Oracle. This should return an output as follows:
to_char
---------
100
(1 row)
The function converts the specified INT value into the STRING representation using the specified format. Using the “9999” format formats the string with four digits and padding with leading zeros if necessary.
Method 4:
In MySQL, we can use the CONCAT or CONCAT_WS function to convert an INT to a STRING and concatenate it with other values if needed.
An example is as follows:
SELECT CONCAT_WS('', 100);
This should convert the INT into STRING.
Method 5:
In SQL Server, we can use the STR function to convert an INT to a STRING with optional formatting and precision.
This should convert an INT to STRING with a total width of 6 characters and 2 decimal places.
Conclusion
This guide walked you through the various methods of converting an INT value into a STRING representation using SQL.
