LeetCode SQL - Scalar Fuction

Table of contents

Scalar Functions

String Functions

Case Conversion

SELECT 
    UPPER('hello') AS uppercase,           -- 'HELLO'
    LOWER('WORLD') AS lowercase,           -- 'world'
    INITCAP('hello world') AS titlecase;   -- 'Hello World' (PostgreSQL)

String Extraction

SELECT 
    SUBSTRING('PostgreSQL', 1, 4) AS sub,        -- 'Post'
    LEFT('database', 4) AS left_part,            -- 'data'
    RIGHT('database', 4) AS right_part,          -- 'base'
    SUBSTR('hello', 2, 3) AS substr_part;        -- 'ell' (start at position 2)

String Manipulation

SELECT
    CONCAT('Hello', ' ', 'World') AS concat1,           -- 'Hello World'
    'Hello' || ' ' || 'World' AS concat2,               -- 'Hello World' (PostgreSQL)
    TRIM('  spaces  ') AS trimmed,                      -- 'spaces'
    LTRIM('  left') AS left_trim,                       -- 'left'
    RTRIM('right  ') AS right_trim,                     -- 'right'
    REPLACE('Hello World', 'World', 'SQL') AS replaced, -- 'Hello SQL'
    REVERSE('hello') AS reversed;                       -- 'olleh'

String Search

SELECT
    POSITION('SQL' IN 'PostgreSQL') AS pos,        -- 7
    STRPOS('PostgreSQL', 'SQL') AS strpos,         -- 7 (PostgreSQL)
    LENGTH('hello') AS len,                        -- 5
    CHAR_LENGTH('hello') AS char_len;              -- 5

String Padding

SELECT
    LPAD('42', 5, '0') AS left_padded,    -- '00042'
    RPAD('42', 5, '0') AS right_padded;   -- '42000'

Pattern Matching

SELECT
    name,
    name LIKE '%son' AS ends_with_son,              -- Boolean
    name ILIKE '%JOHN%' AS contains_john_case_insensitive,  -- PostgreSQL
    REGEXP_REPLACE(email, '@.*', '@hidden.com') AS masked_email;

Real Example:

-- Clean and format names
SELECT
    user_id,
    TRIM(INITCAP(first_name)) AS formatted_first_name,
    UPPER(LEFT(last_name, 1)) || LOWER(SUBSTRING(last_name, 2)) AS formatted_last_name
FROM users;

Numeric Functions

Rounding and Truncation

SELECT
    ROUND(3.14159, 2) AS rounded,         -- 3.14
    CEIL(3.14) AS ceiling,                -- 4
    CEILING(3.14) AS ceiling_alt,         -- 4
    FLOOR(3.99) AS floor,                 -- 3
    TRUNC(3.14159, 2) AS truncated;       -- 3.14

Absolute and Sign

SELECT
    ABS(-42) AS absolute,                 -- 42
    SIGN(-10) AS sign_negative,           -- -1
    SIGN(0) AS sign_zero,                 -- 0
    SIGN(10) AS sign_positive;            -- 1

Power and Roots

SELECT
    POWER(2, 3) AS power,                 -- 8
    SQRT(16) AS square_root,              -- 4
    CBRT(27) AS cube_root,                -- 3 (PostgreSQL)
    EXP(1) AS exponential,                -- 2.718...
    LN(2.718) AS natural_log;             -- 1

Trigonometry

SELECT
    PI() AS pi,                           -- 3.14159...
    SIN(PI()/2) AS sine,                  -- 1
    COS(0) AS cosine,                     -- 1
    TAN(PI()/4) AS tangent;               -- 1

Random

SELECT
    RANDOM() AS random_decimal,           -- 0.0 to 1.0 (PostgreSQL)
    FLOOR(RANDOM() *100) AS random_int;  -- 0 to 99

Modulo

SELECT
    MOD(10, 3) AS modulo,                 -- 1
    10 % 3 AS modulo_operator;            -- 1

Real Example:

-- Calculate discounted price
SELECT
    product_id,
    price,
    ROUND(price* 0.85, 2) AS discounted_price,
    CEIL(price * 0.85) AS price_rounded_up
FROM products;

Date and Time Functions

Current Date/Time

SELECT
    CURRENT_DATE AS today,                              -- 2025-10-03
    CURRENT_TIME AS now_time,                           -- 14:30:25
    CURRENT_TIMESTAMP AS now,                           -- 2025-10-03 14:30:25
    NOW() AS now_alt,                                   -- 2025-10-03 14:30:25 (PostgreSQL)
    CLOCK_TIMESTAMP() AS exact_now;                     -- Changes during query execution

Extraction

SELECT
    EXTRACT(YEAR FROM CURRENT_DATE) AS year,            -- 2025
    EXTRACT(MONTH FROM CURRENT_DATE) AS month,          -- 10
    EXTRACT(DAY FROM CURRENT_DATE) AS day,              -- 3
    EXTRACT(HOUR FROM CURRENT_TIMESTAMP) AS hour,       -- 14
    EXTRACT(DOW FROM CURRENT_DATE) AS day_of_week,      -- 0-6 (0=Sunday)
    EXTRACT(EPOCH FROM CURRENT_TIMESTAMP) AS unix_time; -- Seconds since 1970

Date Parts (PostgreSQL)

SELECT
    DATE_PART('year', CURRENT_DATE) AS year,
    DATE_PART('quarter', CURRENT_DATE) AS quarter,
    DATE_PART('week', CURRENT_DATE) AS week_number;

Date Arithmetic

SELECT
    CURRENT_DATE + INTERVAL '7 days' AS next_week,
    CURRENT_DATE - INTERVAL '1 month' AS last_month,
    CURRENT_DATE + INTERVAL '2 years' AS two_years_later,
    CURRENT_TIMESTAMP + INTERVAL '3 hours' AS three_hours_later;

Date Difference

SELECT
    AGE('2025-10-03', '2020-01-01') AS age,                    -- 5 years 9 mons 2 days
    '2025-10-03'::DATE - '2025-10-01'::DATE AS days_diff,      -- 2
    EXTRACT(DAY FROM '2025-10-03'::DATE - '2025-10-01'::DATE) AS days;

Formatting

SELECT
    TO_CHAR(CURRENT_DATE, 'YYYY-MM-DD') AS iso_date,          -- '2025-10-03'
    TO_CHAR(CURRENT_DATE, 'Month DD, YYYY') AS formatted,     -- 'October 03, 2025'
    TO_CHAR(CURRENT_DATE, 'Day') AS day_name,                 -- 'Friday'
    TO_CHAR(CURRENT_TIMESTAMP, 'HH24:MI:SS') AS time_24hr;    -- '14:30:25'

Date Truncation

SELECT
    DATE_TRUNC('year', CURRENT_TIMESTAMP) AS year_start,      -- 2025-01-01 00:00:00
    DATE_TRUNC('month', CURRENT_TIMESTAMP) AS month_start,    -- 2025-10-01 00:00:00
    DATE_TRUNC('week', CURRENT_TIMESTAMP) AS week_start;      -- 2025-09-29 00:00:00

Real Example:

-- Find orders from last 30 days
SELECT
    order_id,
    order_date,
    CURRENT_DATE - order_date AS days_ago,
    TO_CHAR(order_date, 'Day, Month DD, YYYY') AS formatted_date
FROM orders
WHERE order_date >= CURRENT_DATE - INTERVAL '30 days';

Conversion Functions

Type Casting

SELECT
    CAST('123' AS INTEGER) AS to_int,                  -- 123
    '123'::INTEGER AS to_int_postgres,                 -- 123 (PostgreSQL shorthand)
    CAST(123.45 AS INTEGER) AS float_to_int,           -- 123
    CAST('2025-10-03' AS DATE) AS to_date,
    '2025-10-03'::DATE AS to_date_postgres;

String to Number

SELECT
    TO_NUMBER('123.45', '999.99') AS to_num,           -- PostgreSQL
    TO_NUMBER('$1,234.56', 'L9,999.99') AS currency;

Number to String

SELECT
    TO_CHAR(1234.5, '9,999.99') AS formatted_num,      -- ' 1,234.50'
    TO_CHAR(1234.5, 'FM9,999.99') AS no_padding;       -- '1,234.50' (FM = fill mode)

Date Conversions

SELECT
    TO_DATE('2025-10-03', 'YYYY-MM-DD') AS str_to_date,
    TO_TIMESTAMP('2025-10-03 14:30:25', 'YYYY-MM-DD HH24:MI:SS') AS str_to_timestamp;

Real Example:

-- Convert string prices to numbers for calculation
SELECT
    product_id,
    TO_NUMBER(REPLACE(price_string, '$', ''), '9999.99') AS price_numeric,
    CAST(quantity_string AS INTEGER) AS quantity
FROM raw_data;

Conditional Functions

CASE Expression

SELECT
    grade,
    CASE
        WHEN grade >= 90 THEN 'A'
        WHEN grade >= 80 THEN 'B'
        WHEN grade >= 70 THEN 'C'
        WHEN grade >= 60 THEN 'D'
        ELSE 'F'
    END AS letter_grade
FROM students;

Simple CASE

SELECT
    status,
    CASE status
        WHEN 'pending' THEN 'Processing'
        WHEN 'shipped' THEN 'In Transit'
        WHEN 'delivered' THEN 'Completed'
        ELSE 'Unknown'
    END AS status_description
FROM orders;

COALESCE (Return First Non-NULL)

SELECT
    COALESCE(phone, mobile, email, 'No contact') AS contact_info,
    COALESCE(discount_price, regular_price) AS final_price,
    COALESCE(NULL, NULL, 'default') AS result;  -- 'default'

NULLIF (Return NULL if Equal)

SELECT
    NULLIF(value, 0) AS safe_divisor,           -- Avoids division by zero
    sales / NULLIF(days, 0) AS avg_daily_sales;

GREATEST/LEAST

SELECT
    GREATEST(10, 20, 5) AS maximum,             -- 20
    LEAST(10, 20, 5) AS minimum;                -- 5

Real Example:

-- Handle NULL values and calculate with fallbacks
SELECT
    customer_id,
    COALESCE(email, phone, 'No contact info') AS contact,
    CASE
        WHEN total_purchases > 1000 THEN 'VIP'
        WHEN total_purchases > 500 THEN 'Premium'
        ELSE 'Standard'
    END AS customer_tier,
    total_purchases / NULLIF(purchase_count, 0) AS avg_purchase_value
FROM customers;

NULL Handling Functions

COALESCE (Multiple Arguments)

SELECT
    COALESCE(col1, col2, col3, 'default') AS first_non_null;

NULLIF

SELECT
    NULLIF(value, '') AS empty_to_null,         -- Convert empty string to NULL
    NULLIF(value, 0) AS zero_to_null;           -- Convert 0 to NULL

IS NULL / IS NOT NULL

SELECT
    CASE WHEN email IS NULL THEN 'Missing' ELSE email END AS email_status,
    CASE WHEN phone IS NOT NULL THEN 'Has phone' ELSE 'No phone' END AS phone_status;

Real Example:

-- Clean data: replace empty strings with NULL, then use default
SELECT
    user_id,
    COALESCE(NULLIF(TRIM(email), ''), '<noemail@example.com>') AS clean_email
FROM users;

Comparison and Logic Functions

Boolean Functions

SELECT
    value BETWEEN 10 AND 20 AS in_range,        -- TRUE/FALSE
    value IN (1, 2, 3, 5, 8) AS in_list,
    name LIKE 'J%' AS starts_with_j,
    email ~* '@gmail\.com$' AS is_gmail;        -- Regex (PostgreSQL)

Comparison

SELECT
    CASE WHEN a = b THEN 'Equal' ELSE 'Not equal' END,
    CASE WHEN a > b THEN a ELSE b END AS max_value,
    a IS DISTINCT FROM b AS different_including_null;  -- PostgreSQL

JSON Functions (PostgreSQL)

JSON Extraction

SELECT
    data->>'name' AS name,                      -- Get text value
    data->'address'->>'city' AS city,           -- Nested access
    data#>>'{address,city}' AS city_alt,        -- Path syntax
    jsonb_array_length(data->'items') AS item_count;

JSON Building

SELECT
    json_build_object('id', id, 'name', name) AS json_obj,
    json_agg(name) AS json_array;

Real Example:

-- Extract user data from JSON column
SELECT
    user_id,
    profile->>'firstName' AS first_name,
    profile->>'lastName' AS last_name,
    (profile->>'age')::INTEGER AS age
FROM user_profiles;

Hashing and Encoding

MD5 Hash

SELECT
    MD5('password') AS hash,                    -- '5f4dcc3b5aa765d61d8327deb882cf99'
    MD5(email::TEXT) AS hashed_email;

Encoding

SELECT
    ENCODE('hello', 'base64') AS base64,        -- 'aGVsbG8='
    DECODE('aGVsbG8=', 'base64') AS decoded;    -- 'hello'

Practical Combination Examples

Example 1: User Registration Cleanup

SELECT
    user_id,
    LOWER(TRIM(email)) AS clean_email,
    INITCAP(TRIM(first_name)) AS formatted_first_name,
    UPPER(LEFT(country_code, 2)) AS country,
    COALESCE(phone, 'No phone') AS contact_phone,
    EXTRACT(YEAR FROM AGE(CURRENT_DATE, birth_date)) AS age
FROM user_registrations;

Example 2: E-commerce Analytics

SELECT
    order_id,
    ROUND(price *quantity* (1 - COALESCE(discount, 0)), 2) AS final_amount,
    CASE
        WHEN final_amount > 1000 THEN 'High'
        WHEN final_amount > 100 THEN 'Medium'
        ELSE 'Low'
    END AS order_value_tier,
    TO_CHAR(order_date, 'YYYY-MM') AS order_month,
    CURRENT_DATE - order_date AS days_since_order
FROM orders;

Example 3: Data Quality Report

SELECT
    'Email' AS field,
    COUNT(*) AS total,
    COUNT(email) AS non_null,
    COUNT(NULLIF(TRIM(email), '')) AS non_empty,
    ROUND(100.0 * COUNT(email) / COUNT(*), 2) AS completeness_pct
FROM users
UNION ALL
SELECT
    'Phone',
    COUNT(*),
    COUNT(phone),
    COUNT(NULLIF(TRIM(phone), '')),
    ROUND(100.0 * COUNT(phone) / COUNT(*), 2)
FROM users;

Min/Max of row

1699. Number of Calls Between Two Persons

See LeetCode SQL Relationships - 1699. Number of Calls Between Two Persons

1384. Total Sales Amount by Year

See LeetCode SQL Time Series - 1384. Total Sales Amount by Year