MySQL 20 Most Used Functions — With Real Examples for Every Developer

-- Combine first and last name
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM users;
-- → 'Alice Smith'

-- CONCAT_WS: with separator (skips NULLs automatically)
SELECT CONCAT_WS(', ', city, state, country) AS address FROM locations;
-- → 'San Francisco, CA, USA'

-- Build dynamic messages
SELECT CONCAT('Welcome back, ', first_name, '! You have ', unread_count, ' new messages.') AS greeting
FROM users
WHERE unread_count > 0;

-- SUBSTRING(str, start, length)
SELECT SUBSTRING('Hello World', 7, 5);  -- → 'World'
SELECT SUBSTRING(email, 1, LOCATE('@', email) - 1) AS username FROM users;
-- Extracts everything before the @ in an email

-- Negative positions count from the end
SELECT SUBSTRING('invoice_2026_03.pdf', -7, 7);  -- → '03.pdf'

-- Extract year from a date string
SELECT SUBSTRING(order_date, 1, 4) AS year FROM orders;
-- → '2026'

SELECT REPLACE('Hello World', 'World', 'MySQL');  -- → 'Hello MySQL'

-- Useful for cleaning data
UPDATE products SET description = REPLACE(description, 'http://', 'https://');

-- Remove unwanted characters from phone numbers
UPDATE contacts SET phone = REPLACE(REPLACE(REPLACE(phone, '-', ''), '(', ''), ')', '');
-- '(415) 555-1234' → '4155551234'

-- Strip HTML tags (basic — for simple cases only)
SELECT REPLACE(REPLACE(content, '<b>', ''), '</b>', '') AS clean_text FROM posts;

SELECT TRIM('  hello world  ');   -- → 'hello world'
SELECT LTRIM('  hello');           -- → 'hello'  (left only)
SELECT RTRIM('hello  ');           -- → 'hello'  (right only)

-- Remove specific characters
SELECT TRIM(LEADING '0' FROM '000123');   -- → '123'
SELECT TRIM(TRAILING '.' FROM 'price.'); -- → 'price'

-- Clean imported CSV data that has extra spaces
UPDATE products SET name = TRIM(name) WHERE name != TRIM(name);

SELECT UPPER('hello');  -- → 'HELLO'
SELECT LOWER('HELLO');  -- → 'hello'

-- Normalize emails before storing
INSERT INTO users (email) VALUES (LOWER('Alice@Example.COM'));

-- Case-insensitive search using LOWER
SELECT * FROM products
WHERE LOWER(name) LIKE LOWER('%iPhone%');

-- Format for display: capitalize first letter
SELECT CONCAT(UPPER(LEFT(name, 1)), LOWER(SUBSTRING(name, 2))) AS formatted_name
FROM categories;

SELECT LENGTH('hello');       -- → 5 (bytes)
SELECT CHAR_LENGTH('hello');  -- → 5 (characters, handles multi-byte UTF-8)

-- Find users with unusually short usernames
SELECT username FROM users WHERE CHAR_LENGTH(username) < 3;

-- Validate phone number format
SELECT phone FROM contacts WHERE CHAR_LENGTH(REPLACE(phone, '-', '')) != 10;

-- Find truncated text (UTF-8 emoji is 4 bytes but 1 character)
SELECT content FROM posts WHERE LENGTH(content) != CHAR_LENGTH(content);
-- These rows contain multi-byte characters (emoji, CJK, Arabic)

SELECT LOCATE('World', 'Hello World');  -- → 7
SELECT INSTR('Hello World', 'World');   -- → 7  (same, different syntax)

-- Check if email is valid (has @)
SELECT email FROM users WHERE LOCATE('@', email) = 0;  -- missing @

-- Extract domain from email
SELECT SUBSTRING(email, LOCATE('@', email) + 1) AS domain FROM users;
-- 'alice@gmail.com' → 'gmail.com'

-- Check if URL contains a specific path
SELECT url FROM pages WHERE LOCATE('/blog/', url) > 0;

SELECT NOW();      -- → '2026-03-25 14:32:15'  (date + time)
SELECT CURDATE();  -- → '2026-03-25'            (date only)
SELECT CURTIME();  -- → '14:32:15'              (time only)

-- Record creation time
INSERT INTO orders (user_id, created_at) VALUES (123, NOW());

-- SYSDATE() vs NOW(): NOW() is fixed for the duration of the query;
-- SYSDATE() updates with the actual clock time per row
SELECT NOW(), SLEEP(1), NOW();     -- NOW() same both times
SELECT SYSDATE(), SLEEP(1), SYSDATE();  -- SYSDATE() differs by 1 second

SELECT DATE_FORMAT(created_at, '%M %d, %Y') AS formatted FROM orders;
-- → 'March 25, 2026'

SELECT DATE_FORMAT(NOW(), '%Y-%m') AS month_key;
-- → '2026-03'  (useful for grouping by month)

-- Common format codes:
-- %Y = 4-digit year, %y = 2-digit year
-- %m = month (01-12), %M = month name
-- %d = day (01-31), %D = day with suffix (1st, 2nd)
-- %H = 24h hour, %h = 12h hour, %i = minutes, %s = seconds
-- %W = weekday name, %w = weekday number (0=Sunday)

-- Format for CSV export
SELECT DATE_FORMAT(created_at, '%Y/%m/%d %H:%i') AS export_date FROM orders;
-- → '2026/03/25 14:32'

-- Days between two dates
SELECT DATEDIFF('2026-12-31', '2026-03-25');  -- → 281

-- More granular: TIMESTAMPDIFF(unit, start, end)
SELECT TIMESTAMPDIFF(YEAR, birth_date, CURDATE()) AS age FROM users;
SELECT TIMESTAMPDIFF(MONTH, subscription_start, NOW()) AS months_subscribed FROM users;
SELECT TIMESTAMPDIFF(MINUTE, created_at, NOW()) AS minutes_ago FROM orders;
SELECT TIMESTAMPDIFF(HOUR, last_login, NOW()) AS hours_since_login FROM users;

-- Orders older than 30 days that are still pending
SELECT * FROM orders
WHERE status = 'pending'
  AND DATEDIFF(NOW(), created_at) > 30;

SELECT DATE_ADD(NOW(), INTERVAL 30 DAY);    -- 30 days from now
SELECT DATE_SUB(NOW(), INTERVAL 1 MONTH);  -- 1 month ago
SELECT DATE_ADD(NOW(), INTERVAL 2 HOUR);   -- 2 hours from now

-- Find orders from the last 7 days
SELECT * FROM orders WHERE created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY);

-- Calculate subscription expiry
SELECT
  user_id,
  subscription_start,
  DATE_ADD(subscription_start, INTERVAL 1 YEAR) AS expires_at
FROM subscriptions;

-- Find expiring subscriptions (next 14 days)
SELECT * FROM subscriptions
WHERE expires_at BETWEEN NOW() AND DATE_ADD(NOW(), INTERVAL 14 DAY);

-- Count rows
SELECT COUNT(*) AS total_orders FROM orders;
SELECT COUNT(DISTINCT user_id) AS unique_customers FROM orders;

-- Numeric aggregates
SELECT
  SUM(total)    AS revenue,
  AVG(total)    AS avg_order,
  MIN(total)    AS smallest_order,
  MAX(total)    AS largest_order
FROM orders
WHERE created_at >= '2026-01-01';

-- Group by with aggregates
SELECT
  user_id,
  COUNT(*)      AS order_count,
  SUM(total)    AS total_spent,
  AVG(total)    AS avg_order_value,
  MAX(total)    AS biggest_order
FROM orders
GROUP BY user_id
ORDER BY total_spent DESC
LIMIT 10;

-- Conditional counting with CASE
SELECT
  COUNT(CASE WHEN status = 'completed' THEN 1 END) AS completed,
  COUNT(CASE WHEN status = 'pending'   THEN 1 END) AS pending,
  COUNT(CASE WHEN status = 'failed'    THEN 1 END) AS failed
FROM orders;

-- Combine all tags for each post into one string
SELECT
  post_id,
  GROUP_CONCAT(tag_name ORDER BY tag_name SEPARATOR ', ') AS tags
FROM post_tags
JOIN tags USING (tag_id)
GROUP BY post_id;
-- → post_id: 1, tags: 'api, javascript, tutorial'

-- List all product SKUs for an order
SELECT
  o.order_id,
  GROUP_CONCAT(p.sku ORDER BY p.sku SEPARATOR ' | ') AS product_skus
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
GROUP BY o.order_id;

-- Count distinct values in the concat
SELECT
  category,
  GROUP_CONCAT(DISTINCT author ORDER BY author) AS all_authors
FROM articles
GROUP BY category;

-- IF(condition, true_value, false_value)
SELECT name, IF(stock > 0, 'In Stock', 'Out of Stock') AS availability
FROM products;

-- IFNULL: return fallback if NULL
SELECT IFNULL(phone, 'N/A') AS phone FROM users;
SELECT IFNULL(discount, 0) AS discount FROM orders;  -- treat NULL discount as 0

-- NULLIF: return NULL if equal (avoid division by zero)
SELECT total / NULLIF(quantity, 0) AS unit_price FROM orders;
-- Returns NULL instead of "Division by zero" error

-- COALESCE: return first non-NULL value (more flexible than IFNULL)
SELECT COALESCE(preferred_name, nickname, first_name, 'Unknown') AS display_name
FROM users;

SELECT
  order_id,
  total,
  CASE
    WHEN total >= 500  THEN 'Gold'
    WHEN total >= 100  THEN 'Silver'
    WHEN total >= 10   THEN 'Bronze'
    ELSE 'Standard'
  END AS tier
FROM orders;

-- CASE for status code mapping
SELECT
  order_id,
  CASE status_code
    WHEN 1 THEN 'Pending'
    WHEN 2 THEN 'Processing'
    WHEN 3 THEN 'Shipped'
    WHEN 4 THEN 'Delivered'
    WHEN 5 THEN 'Cancelled'
    ELSE 'Unknown'
  END AS status_label
FROM orders;

-- CASE in aggregations: pivot-like results
SELECT
  DATE_FORMAT(created_at, '%Y-%m') AS month,
  SUM(CASE WHEN country = 'US' THEN total ELSE 0 END) AS us_revenue,
  SUM(CASE WHEN country = 'UK' THEN total ELSE 0 END) AS uk_revenue,
  SUM(CASE WHEN country = 'CA' THEN total ELSE 0 END) AS ca_revenue
FROM orders
GROUP BY month
ORDER BY month;

SELECT ROUND(3.14159, 2);  -- → 3.14
SELECT FLOOR(3.9);          -- → 3  (always rounds down)
SELECT CEIL(3.1);           -- → 4  (always rounds up)
SELECT TRUNCATE(3.9999, 2); -- → 3.99  (truncate, no rounding)

-- Currency: round to 2 decimal places
SELECT ROUND(price * 1.08, 2) AS price_with_tax FROM products;

-- Banker's rounding behavior: ROUND(2.5) = 3, ROUND(3.5) = 4
-- MySQL rounds away from zero: ROUND(-2.5) = -3

-- Round to nearest $5
SELECT order_id, ROUND(total / 5) * 5 AS rounded_to_5 FROM orders;

SELECT MOD(10, 3);     -- → 1  (remainder, same as 10 % 3)
SELECT ABS(-42);        -- → 42 (absolute value)
SELECT RAND();          -- → 0.6823... (random float between 0 and 1)

-- Random sample of 100 rows (for small tables)
SELECT * FROM users ORDER BY RAND() LIMIT 100;

-- Even/odd check
SELECT id, IF(MOD(id, 2) = 0, 'Even', 'Odd') AS parity FROM items;

-- Calculate percentage
SELECT
  category,
  COUNT(*) AS cnt,
  ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (), 2) AS percentage
FROM products
GROUP BY category;

SELECT POWER(2, 10);    -- → 1024
SELECT SQRT(144);        -- → 12
SELECT LOG(100) / LOG(10); -- → 2  (log base 10 of 100)
SELECT LOG2(256);        -- → 8
SELECT PI();             -- → 3.141592653589793

-- Calculate compound growth
SELECT
  product_id,
  initial_price,
  ROUND(initial_price * POWER(1 + growth_rate, years), 2) AS projected_price
FROM product_forecasts;

-- Rank users by total spend
SELECT
  user_id,
  total_spent,
  ROW_NUMBER() OVER (ORDER BY total_spent DESC) AS row_num,
  RANK()       OVER (ORDER BY total_spent DESC) AS rank_num,  -- gaps on ties
  DENSE_RANK() OVER (ORDER BY total_spent DESC) AS dense_rank  -- no gaps on ties
FROM user_totals;

-- Get top 3 products per category
SELECT * FROM (
  SELECT
    product_id,
    category,
    revenue,
    ROW_NUMBER() OVER (PARTITION BY category ORDER BY revenue DESC) AS rn
  FROM product_revenue
) ranked
WHERE rn <= 3;

-- Running total of revenue by date
SELECT
  order_date,
  daily_revenue,
  SUM(daily_revenue) OVER (ORDER BY order_date ROWS UNBOUNDED PRECEDING) AS running_total
FROM daily_sales;

-- 7-day moving average
SELECT
  order_date,
  daily_revenue,
  AVG(daily_revenue) OVER (
    ORDER BY order_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS moving_avg_7d
FROM daily_sales;

-- Percentage of total per category
SELECT
  product_id,
  category,
  revenue,
  ROUND(revenue / SUM(revenue) OVER (PARTITION BY category) * 100, 2) AS pct_of_category
FROM product_revenue;