str

Description

Returns the character equivalent of the specified number, and pads the output with a character or numeric to the specified length.

Syntax

str(approx_numeric[, length [, decimal]])

Parameters

approx_numeric

is any approximate numeric (float, real, or double precision) column name, variable, or constant expression.

length

sets the number of characters to be returned (including the decimal point, all digits to the right and left of the decimal point, and blanks). The default is 10.

decimal

sets the number of decimal digits to be returned. The default is 0. Also can be used to pad the output with a character or numeric to the specified length.

When you specify a character or numeric as a literal string, the character or numeric is used as padding for the field. When you specify a numeric value, sets the number of decimal places. The default is 0. When decimal is not set, the field is padded with blanks to the value specified by length.

Examples

Example 1

When decimal is set as the string literal '0', the field is padded with 0 to a length of 10 spaces.

select str(5,10,'0')
----------------
  0000000005

Example 2

When decimal is a numeric of 5, the number of decimal places is set to 5.

select str(5,10,5)
----------------
   5.00000

Example 3

When decimal is set to the character of '_', the original value is maintained and the field is padded with the specified character to a length of 16 spaces.

select str(12.34500,16,'_')
----------------
________12.34500

Example 4

Without decimal set, the floating number is set to zero decimal places and the field is padded with blanks to a length of 16 spaces.

select str(12.34500e,16)
----------------
              12

Example 5

With decimal set to a numeric, the floating number is processed to 7 decimal places and the field is padded with blanks to a length of 16 spaces.

select str(12.34500e,16,7)
----------------
      12.3450000

Example 6

Specify a prefix character and process a floating number to a specified number of decimal places using these examples:

select str(convert(numeric(10,2),12.34500e),16,'-')
----------------
-----------12.35

select str(convert(numeric(10,8),12.34500e),16,'-')
----------------
-----12.34500000

Usage

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

Any user can execute str.

See also

Documentation Transact-SQL Users Guide

Functions abs, ceiling, floor, round, sign