SQL Server STR Function

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;
Code language: SQL (Structured Query Language) (sql)

Here is the error message:

Conversion failed when converting the varchar value 'index' to data type int.
Code language: SQL (Structured Query Language) (sql)

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;
Code language: SQL (Structured Query Language) (sql)

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 ] ] )  
Code language: SQL (Structured Query Language) (sql)

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) result
Code language: SQL (Structured Query Language) (sql)

Here is the output:

result
------
123.46

(1 row affected)Code language: CSS (css)

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;
Code language: SQL (Structured Query Language) (sql)

The following shows the output:

result
------
**

(1 row affected)

Another example of the insufficient length is:

SELECT STR(987.65,1) result;
Code language: SQL (Structured Query Language) (sql)

Here is the output:

result
------
*

(1 row affected)

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