Convert Int to String

There are two different functions that can be used when converting an integer to a string. One is CAST and the other is CONVERT. Either of these functions can be used with the exact same result when converting to a string. The only difference with the CONVERT function is that it takes an extra optional parameter for style which can affect the way certain data types are displayed after they are converted (an example is with date/time formats).

The common need to convert an INT to a string is to then concatenate it with either another int or an existing string.

Here is a simple example:

SELECT CAST(12345 AS VARCHAR(11))

And the output:
cast_int_to_varchar

Here we are casting the int to a varchar(11). This is a safe value for us to convert to because the maximum integer value is -2147483648.

We see if we try to convert an integer to a smaller string, it returns back an asterisks (*) meaning an error has occurred.

SELECT CAST(-2147483648 AS VARCHAR(10))

convert_int_to_varchar_error

So it is important to choose a data type that is large enough.

With that said, I have seen the following work as well, however I personally would not run this in production in case it is deprecated in a future version of SQL.

SELECT CAST(-2147483648 AS VARCHAR)

Featured Articles

 Site Author

  • Thanks for visiting!