SQL STR Function

STR converts a number to a rounded, right-aligned string.

STR can optionally specify a length and a number of decimal places.

Leading spaces are added to the return value if the length is larger than the result.

Example

#

The example converts a number to a rounded string.

SELECT STR(123.45, 5, 1) AS 'Converted'
Result:  1 record
Converted
123.5

Syntax

Syntax of the STR function.

STR(number [,length, decimals])

number -- a number with or without decimals.

length -- optional, the returned length including decimals, sign, digits, and spaces.

decimals -- optional, the number of decimal places of the float value.


More Examples

STR with column

PRODUCT
Id
ProductName
SupplierId
UnitPrice
Package
IsDiscontinued
Problem: List products with unit prices rounded to 1 decimal. Sort by price from high to low.
SELECT ProductName, UnitPrice,
       STR(UnitPrice, 5, 1) AS 'Rounded Price'
  FROM Product
 ORDER BY UnitPrice DESC
Result:  78 records
ProductName UnitPrice Rounded Price
Côte de Blaye 263.50 263.5
Thüringer Rostbratwurst 123.79 123.8
Mishi Kobe Niku 97.00   97.0
Sir Rodney's Marmalade 81.00   81.0
Carnarvon Tigers 62.50   62.5
Raclette Courdavault 55.00   55.0
Manjimup Dried Apples 53.00   53.0
Tarte au sucre 49.30   49.3
Ipoh Coffee 46.00   46.0

You may also like



Guides


vsn 3.1