String and Date Functions

Raw data in a database is rarely formatted exactly how the frontend application needs it. Sometimes names are entirely lowercase, or dates are stored down to the millisecond when you only need the year.

SQL provides built-in functions to transform strings and dates directly within your query.

String Functions

Different SQL dialects (Postgres, MySQL, SQL Server) have slightly different names for these functions, but they all provide the same capabilities:

1. Concatenation: Combining strings.
   • Standard SQL / Postgres / SQLite: SELECT first_name || ' ' || last_name
   • MySQL / SQL Server: SELECT CONCAT(first_name, ' ', last_name)
2. Changing Case:
   • SELECT UPPER(name)
   • SELECT LOWER(email)
3. Extracting Substrings: Grabbing a portion of a text string.
   • SELECT SUBSTRING(phone_number FROM 1 FOR 3) (Gets the area code).
4. Length and Trimming:
   • SELECT LENGTH(password)
   • SELECT TRIM(email) (Removes leading and trailing whitespace).

Date Functions

Dates are notoriously difficult in programming. SQL makes it easier by allowing you to extract specific parts of a timestamp.

1. Current Time:
   • SELECT CURRENT_DATE
   • SELECT CURRENT_TIMESTAMP
2. Extracting Parts: If you want to group sales by month, you need to extract the month from the date.
   • Postgres/MySQL: SELECT EXTRACT(MONTH FROM order_date)
   • SQLite: SELECT strftime('%m', order_date)
3. Date Math: Adding or subtracting days.
   • Postgres: SELECT order_date + INTERVAL '7 days'

[!TIP] Whenever possible, format strings and dates in the database query rather than in your application code. Databases are heavily optimized for these operations, and doing it in SQL reduces the amount of data sent over the network!

Try concatenating and capitalizing strings in the interactive lab!