SQL Server STR Function

You organize your data. Now organize your savings.

Set savings goals, track your progress, and stay on course.

Get Savigo for iPhone ›

Summary: in this tutorial, you will learn how to use the SQL Server STR() function to convert numeric value to character value.

SQL Server STR() function overview #

The following statement does not work:

SELECT 'index' + 1;Copy

Here is the error message:

Conversion failed when converting the varchar value 'index' to data type int.Copy

To make it works, you need to convert the number 1 into a string ‘1’ by using the STR() function:

SELECT 
    'index' + STR(1,1,1) result;Copy

The STR() function converts a numeric value to a character value. The following shows the syntax of the STR() function:

STR ( float_expression [ , length [ , decimal ] ] )Copy

In this syntax:

• float_expression is a numeric expression that evaluates to an approximate number with a decimal point.
• length is the total length of the returned string. It includes a decimal point, sign, digits, and spaces. By default, the length is 10 if you skip it. If the length is zero, the function returns NULL.
• decimal is the number of places to the right of the decimal point of the float_expression to be returned. decimal must be less than or equal to 16. If it is bigger than 16, then the function truncates the result to sixteen places to the right of the decimal point.

SQL Server STR() function examples #

This example uses the STR() function to convert a number that consists of six digits and a decimal point to a six-position character string with two decimal places:

SELECT 
    STR(123.456, 6, 2) resultCopy

Here is the output:

result
------
123.46

(1 row affected)Copy

If the length of the number exceeds the specified length, the STR() function returns '**' as shown in the following example:

SELECT STR(987.65,2,2) result;Copy

The following shows the output:

result
------
**

(1 row affected)Copy

Another example of the insufficient length is:

SELECT STR(987.65,1) result;Copy

Here is the output:

result
------
*

(1 row affected)Copy

In this tutorial, you have learned how to use the SQL Server STR() function to convert numeric value to character value.