# 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]
``` 