Overview
This lecture covers how to use the SQL Format function to control the display of numeric output, particularly for currency, decimals, and separators.
Formatting Numbers in SQL
- The
Format function is used to control number appearance, such as rounding, adding dollar signs, or including commas.
- Example: Summing ticket prices and displaying gross, tax, and net revenue values in a readable format.
- Currency fields in Access SQL are automatically formatted with a dollar sign, commas, and two decimal places.
- Calculated fields (like tax) may lose formatting, displaying unnecessary decimal places and no symbols.
- To fix formatting for calculated fields, wrap the calculation in the
Format function.
Using the Format Function
- Syntax:
Format([expression], "format_string") where format_string defines how the output appears.
- Example
format_string: "$###,###.##" adds a dollar sign, comma separators, and two decimal places.
- The hash symbol
# acts as a placeholder for digits.
- Trailing zeros in decimals may be dropped when using
# as a placeholder.
- To always show two decimal places (e.g., $1515.20), replace the final
# with 0 in the format string: "$###,###.00".
Formatting Differences Across SQL Versions
- The illustrated approach works in Microsoft Access SQL.
- Other SQL dialects (like Oracle) have different formatting functions, which will be covered later.
- Formatting can also include percentage signs and other symbols as needed.
Key Terms & Definitions
- Format function — SQL function that specifies how numbers and text should be displayed in query results.
- Placeholder (
#) — Symbol in a format string representing optional digits.
- Trailing zero — A zero at the end of a decimal number, sometimes omitted by formatting.
Action Items / Next Steps
- Review how to write and modify format strings in SQL queries.
- Prepare to learn about table manipulation SQL, including creating, deleting tables, and editing table structures in upcoming videos.