SQL Server STR() Function - Get Number as String

In SQL Server, STR() function returns the numeric data as string.

STR(numeric_expression [,length  [ ,decimal]])

Parameters

numeric_expression: A numric expression with or without a decimal.

length: specifies the total length of the returned string, including decimal point, sign, digits, and spaces. It should be a positive integer. Default length is 10.

Note: The specified length should be greater than or equal to the length of the number before the decimal point plus the number sign if included.

decimal: is the number of places to the right of the decimal point. It must be less than or equal to 16. If the decimal is more than 16, then the result is truncated to 16 places to the right of the decimal point. It is a positive integer.

Return Value

Returns a numeric string of varchar type.

Get Numeric String from a Number

The following STR() function returns the numeric string.

SELECT STR(123.76, 6, 2) AS Result

In the above example, STR(123.76, 6, 2) returns "123.76". 6 is the total length of the result including decimal point, 2 is decimal points in the result.

In the following example, STR(123.76, 6, 1) returns "123.8" because the decimal parameter is 1, so it will rounded up to largest value.

SELECT STR(123.76, 6, 1) AS Result

The STR() function will return different result for different length parameter, as shown below.

SELECT STR(123.76, 5, 2) AS Result1,
	STR(123.76, 4, 2) AS Result2

The following STR() function used with different length and decimal parameters:

SELECT STR(123.76, 6, 1) AS Result1,
	STR(123.76, 6, 2) AS Result2,
	STR(123.76, 7, 3) AS Result3,
	STR(123.76, 8, 4) AS Result4

If the length of the number exceeds the specified length, then it returns '**'.

SELECT STR(123.76, 2, 1) AS Result

If the specified length is bigger than the length of the given number and the decimal is equal to the decimal of the given number, then the number is returned unchanged.

SELECT STR(123.76, 8, 2) AS Result