If you are working with SQL Server database table, sometimes you may want to convert int datatype into string or int to varchar in SQL Server, so in this article, I have mentioned with article, how you can convert int to string in SQL Server using various examples.
Int to string using Cast()
Cast() in SQL Server, is a function that converts a value of one data type to another.
It has the following syntax:
CAST(expression AS datatype(length))
The Cast() function accepts two values, the first is the expression whose data type a user wants to change, and the second is the resulting data type that a user wants.
Here is an example, in which we will convert intger datatype to varchar using SQL Server Cast()
DECLARE @myNumber INT SET @myNumber=112233 SELECT CAST(@myNumber AS varchar(10)) AS Num1
Num1 ________ | | |112233| | |
So, in the above example, we have declared a variable of integer data type and assigned a value to the variable. Next, we are using the
Cast() function to convert the variable to the varchar data type of length 10 from int data-type.
Int to varchar using Convert()
Convert() function in SQL Server can also be used to convert a value of one data type to another.
Convert() is more efficient than Cast() since it allows us to additional formatting styles, so CAST is less powerful and less flexible than CONVERT.
Here is the syntax of Convert()
CONVERT(datatype(length), expression, style)
Style parameter is used to define the formatting option on the resulting expressions and it accepts integer values to define the formatting.
DECLARE @myNumber INT SET @myNumber=112233 SELECT CONVERT(varchar(10),@myNumber) as Num1
Output is same as above.
STR() is another function in SQL Server that accepts number values and returns them as a varchar expression and character data is right-justified, with a specified length and decimal precision.
STR(float_expression [,length [,decimal]])
SELECT STR(123.45, 6, 1) as Num2;
In the above example, STR() converts an expression that is made up of five digits and a decimal point to a six-position character string.
The fractional part of the number is rounded to one decimal place.
You may also like to read: