Lesson 7: Formatting Numbers in SQL

By default, Scalable SQL displays numbers using scientific notation. To change the format, you must add a mask.


Syntax:

Select fieldname<mask>, fieldname<mask>, fieldname<mask>
from  tablename;

Explanation:

• Number masks tell SQL how to display numbers.
• The symbols in the table below define the format mask.
• Enclose number masks in brackets.

Number Masks
9	Display number.
Z	Display number, drop leading zeros.
+	Display a plus in front of positive numbers. Display a negative in front of negative numbers.
-	Display a negative in front of negative numbers. Display nothing in front of positive numbers.
( )	Display negative numbers enclosed in parentheses.
.	Display decimal point.
$	Display dollar sign.
,	Use comma to separate thousands.

Example:
Retrieve the vendor ID, name, and current balance for all vendors. Format the current balance:

1. In the SQL text box, type:

Select VendId, Name, CurrBal [($Z,ZZZ,ZZZ.99)]
from TrnVendor; 

2. Execute the statement by clicking on First, the Run button located to the right of the SQL text box. The results will display on the screen.
3. Click on Stop to return to the original screen.

Results:

VendId	Name	CurrBal
TV001	Wet Off Towels	$13.13
TV002	The Games All Here	$453.17
TV003	BedMakers Linen	$500.00
TV004	Bed Room Furniture, Inc.	$321.70
TV005	Ray Block	$1,001.23
TV006	Mosquito No Bite	$967.24
TV007	Spot Out	$1,606.54