SQLite Expressions: Mastering Functions for Data Manipulation

Built-in Functions

SQLite offers a rich set of built-in functions for various tasks like string manipulation, date calculations, and mathematical operations. These functions can be used directly within expressions. For example, you can use the length() function to find the length of a string within a SELECT statement:

SELECT name, length(name) AS name_length FROM customers;

Here, length(name) is an expression that calculates the string length within the main SELECT statement.

Nesting Functions

SELECT name, UPPER(SUBSTR(name, 1, 1)) || LOWER(SUBSTR(name, 2)) AS formatted_name
FROM customers;

This query uses two functions:

• UPPER capitalizes the output from SUBSTR.
• SUBSTR(name, 1, 1) extracts the first character of the name column.

Then, it concatenates (||) the capitalized first character with the rest of the name converted to lowercase using LOWER(SUBSTR(name, 2)).

Complex Expressions

SQLite allows for complex expressions with multiple nested functions. You can combine mathematical functions, string manipulation, and conditional logic (using functions like coalesce or ifnull) to achieve various data transformations and filtering within your queries.

Benefits

• Reusability
  Functions can be reused across different queries, promoting code maintainability.
• Readability
  Complex logic can be broken down into smaller, more manageable functions, improving code readability.
• Flexibility
  Functions within expressions allow for concise and powerful data manipulation within queries.

• Consider using temporary variables for intermediate results when dealing with very complex expressions.
• Be mindful of nesting depth – excessively nested functions can make queries harder to understand.

---

1. Date Calculations
   

SELECT order_date, strftime('%Y', order_date) AS order_year 
FROM orders;

This code uses the strftime function to extract the year from the order_date column.

1. Conditional formatting
   

SELECT product_name, 
  CASE WHEN price > 100 THEN 'Expensive'
       ELSE 'Affordable'
  END AS price_category
FROM products;

This code uses a CASE expression to categorize products based on their price using a conditional statement.

1. String Manipulation with multiple functions
   

SELECT email, REPLACE(UPPER(email), '.com', '@company.com') AS work_email
FROM users;

This example combines multiple functions:

• REPLACE replaces ".com" with "@company.com".
• UPPER converts the email to uppercase.

1. Mathematical operations with functions
   

SELECT product_id, quantity * unit_price AS total_price
FROM order_items;

This code calculates the total price for each order item by multiplying the quantity with the unit price using basic mathematical operations.

1. Nesting functions with filtering
   

SELECT customer_id, 
  SUM(CASE WHEN order_status = 'shipped' THEN order_amount ELSE 0 END) AS total_shipped_amount
FROM orders
GROUP BY customer_id;

This code uses a nested CASE expression within the SUM aggregate function to calculate the total shipped amount for each customer. It filters orders based on the "shipped" status.

---

1. Subqueries
   

Original

SELECT order_date, strftime('%Y', order_date) AS order_year 
FROM orders;

Using Subquery

SELECT order_date, (SELECT strftime('%Y', order_date)) AS order_year
FROM orders;

This approach can sometimes improve readability for very complex expressions within functions.

1. Derived Tables
   

Original

SELECT email, REPLACE(UPPER(email), '.com', '@company.com') AS work_email
FROM users;

Using Derived Table

WITH formatted_emails AS (
  SELECT email, UPPER(email) AS upper_email
  FROM users
)
SELECT email, REPLACE(upper_email, '.com', '@company.com') AS work_email
FROM formatted_emails;

This approach can improve readability and modularity for complex data transformations.

1. Custom Functions (For Advanced Users)
   

SQLite allows defining custom functions using C/C++. This gives you ultimate control and flexibility for complex logic that might not be achievable with built-in functions. However, this approach requires programming knowledge and is recommended for advanced users.

• Highly specialized tasks
  Explore custom functions (with caution).
• Complex logic
  Consider subqueries or derived tables for improved readability.
• Simple cases
  Use functions within expressions for clarity and conciseness.