-- Character length
SELECT title, LENGTH(title) AS title_length
FROM books;

SELECT
    title,
    UPPER(title) AS uppercase,
    LOWER(title) AS lowercase
FROM books;

-- MySQL, PostgreSQL
SELECT CONCAT(first_name, ' ', last_name) AS full_name
FROM customers;
 
-- PostgreSQL also supports ||
SELECT first_name || ' ' || last_name AS full_name
FROM customers;
 
-- SQL Server
SELECT first_name + ' ' + last_name AS full_name
FROM customers;

-- Join multiple values with a separator
SELECT CONCAT_WS(', ', last_name, first_name) AS name_formatted
FROM authors;

-- SUBSTRING(string, start, length)
SELECT
    title,
    SUBSTRING(title, 1, 10) AS short_title
FROM books;

-- LEFT and RIGHT extract from ends
SELECT
    isbn,
    LEFT(isbn, 3) AS prefix,
    RIGHT(isbn, 4) AS suffix
FROM books;

-- POSITION / LOCATE: Find substring position
SELECT title, POSITION('and' IN title) AS and_position
FROM books
WHERE title LIKE '%and%';
 
-- REPLACE: Substitute text
SELECT
    title,
    REPLACE(title, 'and', '&') AS shortened
FROM books;

-- Remove leading/trailing spaces
SELECT
    TRIM('  hello  ') AS trimmed,
    LTRIM('  hello  ') AS left_trimmed,
    RTRIM('  hello  ') AS right_trimmed;

-- Pad to fixed width
SELECT
    customer_id,
    LPAD(customer_id, 5, '0') AS padded_id
FROM customers;

SELECT
    price,
    ROUND(price, 1) AS rounded_1,
    ROUND(price, 0) AS rounded_0,
    FLOOR(price) AS floor_val,
    CEILING(price) AS ceiling_val
FROM books;

SELECT
    ABS(-15.5) AS absolute,     -- 15.5
    SIGN(-15) AS negative_sign, -- -1
    SIGN(15) AS positive_sign,  -- 1
    SIGN(0) AS zero_sign;       -- 0

SELECT
    POWER(2, 10) AS two_to_ten,  -- 1024
    SQRT(16) AS square_root,     -- 4
    MOD(17, 5) AS remainder,     -- 2 (17 % 5)
    RANDOM() AS random_value;    -- 0.0 to 1.0 (PostgreSQL)

-- Current date/time (varies by database)
SELECT
    CURRENT_DATE AS today,
    CURRENT_TIME AS now_time,
    CURRENT_TIMESTAMP AS now_datetime;
 
-- MySQL alternatives
SELECT
    CURDATE() AS today,
    NOW() AS now_datetime;

SELECT
    order_date,
    EXTRACT(YEAR FROM order_date) AS year,
    EXTRACT(MONTH FROM order_date) AS month,
    EXTRACT(DAY FROM order_date) AS day,
    EXTRACT(HOUR FROM order_date) AS hour
FROM orders;

SELECT
    YEAR(order_date) AS year,
    MONTH(order_date) AS month,
    DAY(order_date) AS day,
    DAYNAME(order_date) AS day_name,
    MONTHNAME(order_date) AS month_name
FROM orders;

-- Add/subtract intervals
SELECT
    order_date,
    order_date + INTERVAL 7 DAY AS plus_week,
    order_date - INTERVAL 1 MONTH AS minus_month
FROM orders;
 
-- PostgreSQL syntax
SELECT order_date + INTERVAL '7 days' FROM orders;

-- MySQL: DATEDIFF returns days
SELECT
    order_date,
    DATEDIFF(CURRENT_DATE, order_date) AS days_ago
FROM orders;
 
-- PostgreSQL: subtract dates directly
SELECT
    order_date,
    CURRENT_DATE - order_date::date AS days_ago
FROM orders;

-- MySQL: DATE_FORMAT
SELECT DATE_FORMAT(order_date, '%M %d, %Y') AS formatted
FROM orders;
-- Result: "January 15, 2024"
 
-- PostgreSQL: TO_CHAR
SELECT TO_CHAR(order_date, 'Month DD, YYYY') AS formatted
FROM orders;

-- Truncate to start of period (PostgreSQL)
SELECT
    order_date,
    DATE_TRUNC('month', order_date) AS month_start,
    DATE_TRUNC('year', order_date) AS year_start
FROM orders;

SELECT
    CAST(price AS INTEGER) AS price_int,
    CAST(customer_id AS VARCHAR(10)) AS id_string,
    CAST('2024-01-15' AS DATE) AS date_val
FROM books;

-- PostgreSQL double-colon syntax
SELECT
    price::INTEGER,
    customer_id::TEXT,
    '2024-01-15'::DATE;
 
-- MySQL CONVERT
SELECT
    CONVERT(price, SIGNED) AS price_int,
    CONVERT(price, CHAR) AS price_string;

-- Implicit: string to number for arithmetic
SELECT '10' + 5;  -- Returns 15 in MySQL
 
-- Explicit: always clear about intent
SELECT CAST('10' AS INTEGER) + 5;

-- Return first non-NULL value
SELECT
    first_name,
    COALESCE(phone, email, 'No contact') AS contact_info
FROM customers;

SELECT
    title,
    COALESCE(stock_quantity, 0) AS stock
FROM books;

-- Return NULL if values are equal
SELECT NULLIF(stock_quantity, 0) AS stock
FROM books;
-- Returns NULL for books with 0 stock

SELECT
    total_amount / NULLIF(item_count, 0) AS avg_item_price
FROM orders;
-- Returns NULL instead of error when item_count is 0

-- MySQL: IFNULL
SELECT IFNULL(phone, 'N/A') AS phone FROM customers;
 
-- Oracle: NVL
SELECT NVL(phone, 'N/A') AS phone FROM customers;
 
-- Standard: COALESCE (works everywhere)
SELECT COALESCE(phone, 'N/A') AS phone FROM customers;

-- Generate a series of numbers (PostgreSQL)
SELECT generate_series(1, 10) AS num;
 
-- Generate date series (PostgreSQL)
SELECT generate_series(
    '2024-01-01'::DATE,
    '2024-01-31'::DATE,
    '1 day'::INTERVAL
) AS date;

-- Generate formatted IDs
SELECT
    order_id,
    CONCAT('ORD-', LPAD(order_id, 6, '0')) AS order_number
FROM orders;

SELECT
    CONCAT(
        UPPER(SUBSTRING(first_name, 1, 1)),
        LOWER(SUBSTRING(first_name, 2))
    ) AS proper_first,
    UPPER(last_name) AS upper_last
FROM customers;

SELECT
    first_name,
    last_name,
    birth_date,
    EXTRACT(YEAR FROM AGE(birth_date)) AS age
FROM authors
WHERE birth_date IS NOT NULL;

SELECT
    CONCAT('Order #', LPAD(order_id, 5, '0')) AS order_number,
    DATE_FORMAT(order_date, '%b %d, %Y') AS order_date,
    CONCAT('$', FORMAT(total_amount, 2)) AS total,
    UPPER(status) AS status
FROM orders;

-- Case-insensitive search with trimmed input
SELECT * FROM customers
WHERE LOWER(TRIM(email)) = LOWER(TRIM('  ALICE.CHEN@email.com  '));