Export (0) Print
Expand All
ABS
ALL
AND
ANY
AVG
bit
COS
COT
DAY
EXP
GO
IN
LEN
LOG
MAX
MIN
NOT
OR
PI
SET
SIN
STR
SUM
TAN
USE
VAR
Expand Minimize

STR

SQL Server 2000

Returns character data converted from numeric data.

Syntax

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

Arguments

float_expression

Is an expression of approximate numeric (float) data type with a decimal point.

length

Is the total length, including decimal point, sign, digits, and spaces. The default is 10.

decimal

Is the number of places to the right of the decimal point.

Return Types

char

Remarks

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). A short float_expression is right-justified in the specified length, and a long float_expression is truncated to the specified number of decimal places. For example, STR(12,10) yields the result of 12, which is right-justified in the result set. However, STR(1223, 2) truncates the result set to **. String functions can be nested.

Note  To convert to Unicode data, use STR inside a CONVERT or CAST conversion function.

Examples
A. Use STR

This example converts an expression consisting of five digits and a decimal point to a six-position character string. The fractional part of the number is rounded to one decimal place.

SELECT STR(123.45, 6, 1)
GO

Here is the result set:

------
 123.5

(1 row(s) affected)

When the expression exceeds the specified length, the string returns ** for the specified length.

SELECT STR(123.45, 2, 2)
GO

Here is the result set:

--
**

(1 row(s) affected)

Even when numeric data is nested within STR, the result is character data with the specified format.

SELECT STR (FLOOR (123.45), 8, 3)
GO

Here is the result set:

--------
 123.000

(1 row(s) affected)
B. Use the STR and CONVERT functions

This example compares the results of STR and CONVERT.

SELECT STR(3.147) AS 'STR',
       STR(3.147, 5, 2) AS '2 decimals',
       STR(3.147, 5, 3) AS '3 decimals'
GO

Here is the result set:

STR        2 decimals 3 decimals 
---------- ---------- ---------- 
         3  3.15      3.147      

(1 row(s) affected)

-- Use CONVERT.
SELECT CONVERT(char(1), 3.147) AS 'CHAR(1)',
       CONVERT(char(3), 3.147) AS 'CHAR(3)',
       CONVERT(char(5), 3.147) AS 'CHAR(5)'
GO

Here is the result set:

CHAR(1) CHAR(3) CHAR(5) 
------- ------- ------- 
(null)  (null)  3.147   

(1 row(s) affected)

See Also

String Functions

Show:
© 2014 Microsoft