Monday, 18 June 2012

Lesson 7: Formatting Numbers in SQL

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

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

  • 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
9Display number.
ZDisplay 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.

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; 
  1. 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.
  2. Click on Stop to return to the original screen.
TV001Wet Off Towels$13.13
TV002The Games All Here$453.17
TV003BedMakers Linen$500.00
TV004Bed Room Furniture, Inc.$321.70
TV005Ray Block$1,001.23
TV006Mosquito No Bite$967.24
TV007Spot Out$1,606.54

No comments:

Post a Comment