SQL Server STR Function


By:

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
simple STR example

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
t-sql str positive rounding

The next example is rounding 0.5 to 1.

SELECT STR(0.5,4) as number
t-sql str rounding to the highest value

The values are similar with negative values.

SELECT STR(-0.49,4) as number
Negative value example with rounding str

If we round -0.5, it will return -1.

SELECT STR(-0.5,4) as number
str rounding negative values

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 truncate value

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
str number of decimals

If we increase the lengthNumber to 3, the value returned is still 1.

SELECT STR(0.978,3,2) as number
str example t-sql sql server

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
rounding t-sql and converting str

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
error
SELECT STR(123,3) + ' Test' as output
query results

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]
query results

Related Articles


Last Update: 11/10/2021




Comments For This Article





download














vote


get free sql tips
agree to terms