SQL STR() Function: Complete Overview

The STR() function returns the number as a string. If there is not enough length or decimal to display the resulting string based on the parameters provided, the function will round off the result.


SQL STR() is a built-in function used for converting numeric value to a character value. In other words, it is used for returning a string representation of a number.


The syntax for the STR() function is below.

STR(number, length, decimals)


Number: The numeric value which is to be converted to a string.

Length: The length of the string which includes all digits, decimals, and signs and as it is completely optional so by default length of the resulting string will be 10.

Decimal: Number of places to the right of the decimal point of the number to be returned. It must be less than or equal to 16 and if it is bigger than 16, then the function truncates the result to sixteen places to the right of the decimal point.

If supplied, the values for length and decimal parameters to STR should be positive.

The number is rounded to an integer by default or if the decimal parameter is 0.

The specified length should be greater than or equal to the part of the number before the decimal point plus the number’s sign (if any).

The short float_expression is right-justified in the specified length, and the long float_expression is truncated to a specified number of decimal places.

For example, STR(12**,10) yields the result of 12. This is right-justified in the result set. However, STR(1223,**2) truncates the result set to **. String functions can be nested.

Example 1




Example 2

SELECT STR (123.5);



Here, the decimal parameter was not provided. So, the result was rounded to 0.

Example 3

SELECT STR (123.5, 5);



Example 4

SELECT STR (123.456, 8, 2);



Here, the result is rounded to 2 decimal places.

Example 5

SELECT STR (123.456, 7, 1);



Here, the result is rounded to 1 decimal place.

Example 6

SELECT STR (987.65, 1);



Here, the length of the number exceeded the specified_length. So, * was returned.

That’s it for SQL STR() Function.

Related Posts

SQL Space Function

SQL Rtrim Function

SQL Reverse Function

SQL Translate Function

SQL Upper Function

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.