Webbo3 Data Analysis Bootcamp · SQL Module · Lesson 4

MySQL Built-in Functions: String Manipulation, Date Arithmetic, Math Operations, and Indexing for Performance

A comprehensive lesson covering the most frequently used MySQL functions for transforming text, calculating dates, performing math, and speeding up queries with indexes, culminating in a multi-table business analysis query.

Raw data in a database is rarely ready for reporting. Names arrive in mixed case. Dates are stored in machine-readable formats but must be displayed as human-readable strings. Numbers need rounding before they appear on a dashboard. Text needs to be concatenated, trimmed, or sliced to fit a report layout. MySQL provides built-in functions for every one of these transformations, and using them inside your SQL queries is far more efficient than pulling raw data into Excel or Python and processing it there. This lesson covers the essential string, date, and math functions you will use daily. It also introduces indexes, the database feature that makes those functions run fast on large tables instead of grinding to a halt. Finally, you will see how all of these pieces fit together in a real multi-table business analysis query.

1. String Functions: CONCAT, LENGTH, UPPER, LOWER, SUBSTRING, and TRIM

String functions let you clean, format, and combine text without leaving the database. If you have ever received a customer list where some names are in uppercase, some in lowercase, and some have accidental leading spaces, these functions are your first line of defense.

CONCAT: combining strings. The CONCAT function joins two or more strings into one. It is the SQL equivalent of using the ampersand in Excel:

SELECT CONCAT(first_name, ' ', last_name) AS full_name
FROM customers;

This returns a single column called full_name containing values like "Chidinma Okafor." If any argument is NULL, CONCAT returns NULL. To handle NULLs gracefully, use CONCAT_WS, which stands for concatenate with separator. It skips NULL values instead of poisoning the entire result:

SELECT CONCAT_WS(' ', first_name, middle_name, last_name) AS full_name
FROM customers;

If middle_name is NULL, CONCAT_WS ignores it and still returns "Chidinma Okafor" instead of NULL. The first argument is the separator, and it is inserted between every pair of non-NULL values.

LENGTH and CHAR_LENGTH: measuring strings. LENGTH returns the number of bytes a string occupies. CHAR_LENGTH returns the number of characters. For English text using Latin-1 encoding, these are the same. For Unicode text, UTF-8, a single character like é or 中 can occupy multiple bytes, so LENGTH and CHAR_LENGTH differ:

SELECT LENGTH('Hello'), CHAR_LENGTH('Hello'); -- 5, 5
SELECT LENGTH('你好'), CHAR_LENGTH('你好'); -- 6, 2

Use CHAR_LENGTH when you care about how many characters a user sees, for example validating that a username is between 3 and 20 characters. Use LENGTH when you care about storage size or network transfer.

UPPER and LOWER: standardizing case. These functions convert text to uppercase or lowercase. They are essential for case-insensitive comparisons and for making reports look consistent:

SELECT UPPER(email) FROM customers;
SELECT LOWER(region) FROM customers;

In MySQL, many collations are case-insensitive by default, so WHERE region = 'lagos' may match 'Lagos' without LOWER. But when you need guaranteed case-insensitive behavior, especially in string comparisons across different database systems, explicitly wrapping both sides in LOWER is the safest approach:

SELECT * FROM customers WHERE LOWER(region) = 'lagos';

SUBSTRING: extracting parts of a string. SUBSTRING, also written as SUBSTR, extracts a portion of a string starting at a specified position:

SELECT SUBSTRING(phone_number, 1, 4) AS network_code
FROM customers;

This extracts four characters starting from position 1. If phone_number is 08031234567, the result is 0803. SUBSTRING can also start from the end using a negative position:

SELECT SUBSTRING(email, -4) AS domain_extension
FROM customers;

This extracts the last four characters. For "user@gmail.com", the result is ".com". Combine SUBSTRING with INSTR or LOCATE to extract dynamic positions, for example everything after the @ symbol in an email.

TRIM: removing unwanted spaces. TRIM removes leading and trailing spaces from a string. It is one of the most important cleaning functions because user input often contains accidental spaces:

SELECT TRIM(' Lagos ') AS cleaned_city; -- Returns 'Lagos'

TRIM only removes spaces by default. To remove other characters, specify them explicitly:

SELECT TRIM(BOTH '0' FROM '00045000') AS cleaned_number; -- Returns '45'

Use LTRIM to remove only leading spaces and RTRIM to remove only trailing spaces. If you are importing data from a CSV or an old system, running TRIM on every text column during your INSERT or UPDATE is standard practice.

2. Date Functions: NOW, CURDATE, YEAR, MONTH, DAY, DATEDIFF, and DATE_FORMAT

Dates are the most common source of confusion in SQL. A date is not a string. It is a structured data type that supports arithmetic, extraction, and formatting. MySQL's date functions let you retrieve the current moment, pull out components like year or month, calculate intervals between events, and format dates for human readers.

NOW, CURDATE, and CURTIME: the current moment. NOW returns the current date and time as a DATETIME value. CURDATE returns only the date portion. CURTIME returns only the time portion:

SELECT NOW() AS current_datetime, CURDATE() AS current_date, CURTIME() AS current_time;

A practical use is finding records created today. Because NOW includes a time component, comparing a DATETIME column directly to CURDATE would fail for afternoon records. Instead, strip the time from the column using the DATE function:

SELECT * FROM orders WHERE DATE(order_date) = CURDATE();

YEAR, MONTH, and DAY: extracting components. These functions pull individual parts from a date or datetime value:

SELECT
  YEAR(order_date) AS order_year,
  MONTH(order_date) AS order_month,
  DAY(order_date) AS order_day
FROM orders;

This is how you group sales by month or filter for a specific year without storing separate columns. You can also use MONTHNAME to get the English name, like January, and DAYNAME to get the weekday name. These are invaluable for report headers and dashboard labels.

DATEDIFF: calculating days between dates. DATEDIFF returns the number of days between two date expressions. It counts calendar days, ignoring time components:

SELECT
  customer_name,
  order_date,
  ship_date,
  DATEDIFF(ship_date, order_date) AS days_to_ship
FROM orders;

The first argument is the later date, the second is the earlier date. If ship_date is June 20 and order_date is June 15, the result is 5. If the order has not shipped and ship_date is NULL, DATEDIFF returns NULL. To find orders that took more than five days to ship:

SELECT * FROM orders WHERE DATEDIFF(ship_date, order_date) > 5;

DATE_FORMAT: formatting dates for display. DATE_FORMAT converts a date into a string using format specifiers. This is how you turn 2026-06-23 into "23 June 2026" or "06/23/2026" directly inside your query:

SELECT
  DATE_FORMAT(order_date, '%d %M %Y') AS formatted_date,
  DATE_FORMAT(order_date, '%W') AS weekday_name
FROM orders;

The most common specifiers are %Y for the four-digit year, %y for the two-digit year, %m for the month number with leading zero, %M for the month name, %d for the day with leading zero, %D for the day with an ordinal suffix like 1st, %H for the hour in 24-hour format, %i for minutes, and %s for seconds. Using DATE_FORMAT in your SQL means your application or report receives the date already formatted, reducing work in Excel or your dashboard tool.

DATE_ADD and DATE_SUB: shifting dates. These functions add or subtract intervals from a date:

SELECT DATE_ADD(CURDATE(), INTERVAL 7 DAY) AS next_week;
SELECT DATE_SUB(CURDATE(), INTERVAL 1 MONTH) AS last_month;

Use these to find records within a rolling window, for example orders from the last 90 days: WHERE order_date >= DATE_SUB(CURDATE(), INTERVAL 90 DAY). This is more robust than hardcoding dates because it updates automatically every day.

3. Math Functions: ROUND, CEIL, FLOOR, ABS, and MOD

Math functions in MySQL handle the numeric transformations that reporting and analysis require. Rounding prices, calculating remainders for batching, and ensuring positive values for variance calculations are all done inside the query before the data reaches your dashboard.

ROUND: controlling decimal precision. ROUND takes a number and the number of decimal places you want to keep:

SELECT ROUND(123.4567, 2) AS rounded_value; -- Returns 123.46

If the digit immediately after your cutoff is 5 or greater, ROUND rounds up. If it is 4 or less, it rounds down. You can also round to negative decimal places to round to tens, hundreds, or thousands:

SELECT ROUND(1234, -2) AS rounded_to_hundreds; -- Returns 1200

CEIL and FLOOR: rounding up and down unconditionally. CEIL, also written as CEILING, always rounds up to the nearest integer. FLOOR always rounds down:

SELECT CEIL(4.2) AS ceiling_result; -- Returns 5
SELECT FLOOR(4.8) AS floor_result; -- Returns 4

Use CEIL when you need to calculate the number of containers or pages required. If a shipment needs 4.2 trucks, you need 5 trucks. Use FLOOR when you have a hard limit. If a customer has 4.8 installment payments remaining, they have completed 4 full payments.

ABS: absolute value. ABS removes the sign from a number, returning its positive magnitude:

SELECT ABS(-25000) AS positive_value; -- Returns 25000

This is useful for calculating variance or deviation where you only care about the magnitude of the difference, not the direction. For example, measuring how far actual sales were from target sales, regardless of whether they were above or below.

MOD: remainder of division. MOD returns the remainder after dividing one number by another:

SELECT MOD(17, 5) AS remainder; -- Returns 2

MOD is useful for grouping rows into batches, assigning every nth record to a different reviewer, or determining if a number is even or odd. MOD(number, 2) returns 0 for even numbers and 1 for odd numbers. You can also use the percent sign operator: 17 % 5 returns 2, which is identical to MOD(17, 5).

4. Indexes: What They Are and When to Use Them

An index is a data structure that MySQL builds and maintains alongside your table to speed up searches. Without an index, when you run a query with a WHERE clause, MySQL must scan every single row in the table, one by one, to find matches. This is called a full table scan. On a table with ten rows, you will not notice. On a table with ten million rows, a full table scan can take minutes or hours. An index changes this by organizing the data in a way that lets MySQL jump directly to the relevant rows, often reducing search time from millions of operations to a handful.

How indexes work conceptually. Think of a book. If you want to find every mention of "Lagos" in a three-hundred-page book, you can read every page. That is a full table scan. Or you can flip to the index at the back, find the word Lagos, and see that it appears on pages 12, 45, and 198. You jump directly to those pages. A database index works the same way. It is a separate, smaller data structure, usually a B-tree, that stores the indexed column values in sorted order along with pointers to the actual rows in the table. When you query that column, MySQL uses the index to find the pointers, then fetches only the relevant rows.

Primary keys are automatically indexed. When you define a PRIMARY KEY on a table, MySQL automatically creates a unique index on that column. This is why looking up a row by its primary key is always fast. You do not need to create a separate index for the primary key. Foreign key columns are also usually good candidates for indexing, though MySQL does not always create them automatically, depending on the storage engine.

Creating an index manually. To create an index on a column you frequently filter or join on, use the CREATE INDEX statement:

CREATE INDEX idx_region ON customers(region);

This creates an index named idx_region on the region column of the customers table. Now a query like SELECT * FROM customers WHERE region = 'Lagos' will use the index instead of scanning the entire table. You can also create a composite index on multiple columns if you frequently filter on them together:

CREATE INDEX idx_region_active ON customers(region, is_active);

This index is most effective when your WHERE clause includes both region and is_active together. MySQL can use it for queries on region alone, but not for queries on is_active alone, because composite indexes work from left to right.

When to create an index. Create indexes on columns that appear frequently in WHERE clauses, JOIN conditions, and ORDER BY clauses. If you run a daily report that filters orders by order_date, index that column. If you join customers to orders on customer_id, ensure both sides of the join are indexed. If you sort results by last_name, an index on that column speeds up the sort. The general rule is: if a query pattern is frequent and the column is selective, meaning it has many distinct values rather than just a few repeated ones, an index will help.

When NOT to create an index. Indexes are not free. Every index consumes disk space. Every INSERT, UPDATE, and DELETE on the table must update the index as well as the table, which slows down write operations. Do not index every column. Do not index columns with very low cardinality, like a boolean is_active column on a million-row table, because the index would map almost every row to the same two values and MySQL may ignore it in favor of a full scan. Do not index columns that are rarely queried. Do not create indexes before you have query patterns to optimize. The best approach is to build your queries first, identify slow ones using EXPLAIN, and then add indexes strategically.

Checking if an index is being used. MySQL provides the EXPLAIN statement to show you the execution plan for a query:

EXPLAIN SELECT * FROM customers WHERE region = 'Lagos';

In the output, look at the type column. If it says ALL, MySQL is doing a full table scan. If it says REF or RANGE, it is using an index. Look at the possible_keys and key columns to see which indexes MySQL considered and which it actually chose. Learning to read EXPLAIN output is a senior analyst skill, but even beginners can use it to verify that an index they created is actually being utilized.

5. MySQL Capstone: Multi-Table Business Analysis Query

This capstone query brings together everything you have learned in the SQL module so far: SELECT, WHERE, JOINs, aggregate functions, and the built-in functions from this lesson. You are working with the NovaMart retail database, which has three tables: customers, orders, and products. Your manager wants a single report that answers: for each product category, what is the total revenue, the average order value, the number of unique customers who purchased, and how many days on average it takes to ship, broken down by month and filtered to the current year?

The tables.

customers: customer_id, first_name, last_name, region, registration_date
orders: order_id, customer_id, product_id, order_date, ship_date, quantity, unit_price
products: product_id, product_name, category, unit_cost

The capstone query.

SELECT
  p.category AS 'Product Category',
  DATE_FORMAT(o.order_date, '%M %Y') AS 'Month',
  COUNT(DISTINCT o.customer_id) AS 'Unique Customers',
  SUM(o.quantity * o.unit_price) AS 'Total Revenue',
  ROUND(AVG(o.quantity * o.unit_price), 2) AS 'Average Order Value',
  ROUND(AVG(DATEDIFF(o.ship_date, o.order_date)), 1) AS 'Avg Days to Ship'
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN products p ON o.product_id = p.product_id
WHERE YEAR(o.order_date) = YEAR(CURDATE())
  AND o.ship_date IS NOT NULL
GROUP BY p.category, YEAR(o.order_date), MONTH(o.order_date)
ORDER BY YEAR(o.order_date), MONTH(o.order_date), 'Total Revenue' DESC;

Breaking down the query. The FROM clause starts with orders, aliased as o for brevity. JOIN connects to customers on customer_id and to products on product_id. Only rows where all three tables have matching records are included. The WHERE clause filters to the current year using YEAR(o.order_date) = YEAR(CURDATE()), and excludes orders that have not shipped yet by requiring ship_date IS NOT NULL. The SELECT clause uses DATE_FORMAT to create a human-readable month label like "June 2026". COUNT(DISTINCT o.customer_id) counts each customer only once per category per month, even if they placed multiple orders. SUM(o.quantity * o.unit_price) calculates total revenue by multiplying quantity and unit_price for every order line, then summing. ROUND(AVG(...), 2) computes the average order value to two decimal places. ROUND(AVG(DATEDIFF(...)), 1) calculates the average shipping time in days, rounded to one decimal place. GROUP BY p.category, YEAR(o.order_date), MONTH(o.order_date) collapses the detailed rows into summary rows, one per category per month. ORDER BY ensures the results appear chronologically, and within each month, the highest revenue category appears first.

This is a professional-grade query. It joins three tables, filters with functions, aggregates with grouping, formats for display, and sorts for readability. It is the kind of query you would write in a business intelligence role, and it is the standard you should aim for by the end of this module.

Using AI to Move Faster in SQL Function Writing

The functions in this lesson are straightforward individually, but combining them into a query like the capstone example requires careful syntax, correct aliasing, and attention to the order of operations. AI can help you draft, debug, and optimize these queries without replacing your need to understand what each function does.

1. Generate complex formatting queries from plain English.
Instead of memorizing every DATE_FORMAT specifier, describe your desired output to Copilot or ChatGPT: "Write a MySQL query that formats order dates as '15th January 2026' and extracts the quarter number for grouping." AI will produce the correct DATE_FORMAT string, %D %M %Y, and the QUARTER function. Your job is to verify the specifiers match your requirement and test the output on a few rows. This is faster than looking up the manual every time.

2. Use AI to suggest indexes for slow queries.
If a query is running slowly, paste the query and your table schema into an AI assistant and ask: "Which columns in this query should be indexed, and what type of index should I create?" AI will identify the columns in your WHERE, JOIN, and ORDER BY clauses and suggest CREATE INDEX statements. It may also warn you about composite index column ordering. Apply the suggestions, then run EXPLAIN to confirm the index is actually used. AI gives you a hypothesis; EXPLAIN gives you proof.

3. Debug function nesting errors.
When you nest functions, for example ROUND(AVG(DATEDIFF(ship_date, order_date)), 1), a single misplaced parenthesis can break the entire query. Paste the error message and your query into AI: "MySQL says I have an error near the DATEDIFF function. What is wrong?" AI can spot missing commas, incorrect argument counts, or functions applied to NULL values that need COALESCE wrapping. This is especially useful when you are learning which functions accept which data types.

4. Generate sample data that exercises edge cases.
To test your string and date functions thoroughly, you need data with NULLs, mixed case, leading spaces, future dates, and leap year dates. Ask AI: "Generate twenty INSERT statements for a customers table with edge cases for TRIM, UPPER, SUBSTRING, and DATEDIFF, including NULL middle names, emails with mixed case, and registration dates on February 29." Insert this data and write queries against it. This deliberate practice ensures your functions handle real-world messiness.

5. Verify AI-generated queries before running them on production data.
AI can confidently generate syntactically invalid SQL or functions that do not exist in your MySQL version. It might suggest REGEXP_REPLACE, which is only available in MySQL 8.0 and later, when you are running 5.7. It might use CEILING instead of CEIL without noting the compatibility difference. Always test AI-generated queries on a small dataset first, check the MySQL version you are running with SELECT VERSION(), and cross-reference the official MySQL documentation for any function you have not used before. AI accelerates your work, but it does not absolve you from understanding the tools.

A habit worth building from this lesson onward: whenever you need to format, calculate, or transform data in SQL, ask whether a built-in function can do it inside the query before exporting to another tool. Processing inside the database is almost always faster and more scalable than pulling raw data into Excel or Python. Use AI to find the right function quickly, but take the time to understand what it returns and how it handles NULLs, because that understanding is what makes you a reliable analyst when the data gets messy.

Next lesson: aggregate functions, GROUP BY, and HAVING for summary statistics.