SQL Server STR Function
By: Daniel Calbimonte
The STR function converts numeric values into character strings.
Syntax
STR(expression, lengthNumber [,NumberOfDecimals])
Parameters
- expression - this is the float number to be converted into string characters.
- lengthNumber - it is the number of characters to be displayed including the decimal point.
- NumberOfDecimals - it is the number of decimal numbers at the right of the decimal point.
Simple STR Example
The following example will convert the number 1234 into a string.
SELECT STR(1234,4) as number
Example to Show How STR Rounds Values
The following example is rounding 0.49 down to 0. This is because we only specified the value to the left of the decimal.
SELECT STR(0.49,4) as number
The next example is rounding 0.5 to 1.
SELECT STR(0.5,4) as number
The values are similar with negative values.
SELECT STR(-0.49,4) as number
If we round -0.5, it will return -1.
SELECT STR(-0.5,4) as number
Truncated Numbers When Using STR Function
If the lengthNumber is less than the expression length, it will return asterisks as shown below.
SELECT STR(433422333,4) as number
STR Examples with Decimals
The following example shows how the STR function works with the value of 0.978, a lengthNumber of 2 and the NumberOfDecimals of 2. Since we only specified 2 values for the length it gets rounded to 1.
SELECT STR(0.978,2,2) as number
If we increase the lengthNumber to 3, the value returned is still 1.
SELECT STR(0.978,3,2) as number
Finally, if lengthNumber is 4, we round the value to 0.98 and return the 4 characters of the number.
SELECT STR(0.978,4,2) as number
Using STR Function with Concatenation
In the example below, we can see how we can use the STR function to change a number to a string and therefore concatenate the values.
SELECT 123 + ' Test' as output
SELECT STR(123,3) + ' Test' as output
Here is another example where we use LEN to figure out how many characters to display for the STR function.
SELECT BusinessEntityID, FirstName, STR(BusinessEntityID, LEN(BusinessEntityID)) + '-' + [FirstName] as output FROM [Person].[Person]
