Master SQL's powerful built-in functions for string manipulation, numeric calculations, date operations, and data aggregation.
By the end of this section, you will be able to:
- Use string functions for text manipulation and formatting
- Apply numeric functions for mathematical calculations
- Work with date/time functions for temporal data analysis
- Implement aggregate functions for data summarization
- Combine multiple functions in complex expressions
- Choose the right function for specific data processing needs
📖 Concepts - Learn the Theory
Start here to understand function categories and usage
- String Functions Overview - Text manipulation concepts
- Numeric Functions Overview - Mathematical operations
- Date Functions Overview - Temporal data handling
- Aggregate Functions Overview - Data summarization
💻 Examples - See Working Code
Run these examples to see functions in action
- 01-string-functions.sql - Text manipulation examples
- 02-numeric-functions.sql - Mathematical calculations
- 03-date-functions.sql - Date/time operations
- 04-aggregate-functions.sql - Data summarization
🏋️ Exercises - Practice Your Skills
Test your understanding with hands-on problems
- Practice Problems - Progressive function exercises
- Solutions - Check your answers
📚 Resources - Quick Reference
Handy function references and cheat sheets
- Function Reference - Complete function catalog
- Common Patterns - Frequently used combinations
- Database Differences - Vendor-specific variations
Follow this recommended sequence:
- 📖 Read Concepts: Start with String Functions
- 💻 Run Examples: Practice with String Examples
- 🏋️ Do Exercises: Test yourself with Practice Problems
- 📚 Reference: Use Function Reference as needed
- Completed 4-Filtering-and-Conditions
- Understanding of SELECT statements and WHERE clauses
- Familiarity with different data types
4-5 hours to complete all materials
- UPPER()/LOWER() - Change text case
- SUBSTRING() - Extract parts of strings
- LENGTH()/LEN() - Get string length
- CONCAT() - Combine strings
- TRIM() - Remove whitespace
- REPLACE() - Replace text patterns
- ROUND() - Round to specified decimals
- CEILING()/FLOOR() - Round up/down to integers
- ABS() - Absolute value
- POWER() - Exponentiation
- SQRT() - Square root
- RAND() - Random numbers
- GETDATE()/NOW() - Current date/time
- DATEADD() - Add time intervals
- DATEDIFF() - Calculate time differences
- YEAR()/MONTH()/DAY() - Extract date parts
- FORMAT() - Format dates for display
- COUNT() - Count rows or values
- SUM() - Add up numeric values
- AVG() - Calculate averages
- MIN()/MAX() - Find minimum/maximum values
- GROUP_CONCAT() - Concatenate grouped values
These functions are essential for:
- Data Cleaning: Standardizing text, fixing formatting issues
- Reporting: Formatting output for business reports
- Analytics: Calculating metrics and KPIs
- Data Transformation: Converting data between formats
- User Interface: Displaying data in user-friendly formats
While SQL is standardized, function syntax can vary:
| Function | SQL Server | MySQL | PostgreSQL | Oracle |
|---|---|---|---|---|
| String Length | LEN() |
LENGTH() |
LENGTH() |
LENGTH() |
| Current Date | GETDATE() |
NOW() |
NOW() |
SYSDATE |
| Substring | SUBSTRING() |
SUBSTRING() |
SUBSTRING() |
SUBSTR() |
| Concatenation | + or CONCAT() |
CONCAT() |
|| or CONCAT() |
|| |
- Practice with Real Data: Use actual datasets to understand function impact
- Combine Functions: Learn to chain functions for complex operations
- Handle NULLs: Understand how functions behave with NULL values
- Check Documentation: Function behavior can vary between database versions
- Performance Awareness: Some functions can impact query performance
← Previous: Filtering & Conditions | Next: Grouping & Aggregation →