Advanced Functions

Q: Q: What happens when CONCAT encounters NULL?

A: In MySQL, CONCAT returns NULL when it encounters NULL. To treat NULL as an empty string, use COALESCE: CONCAT(COALESCE(first_name, ''), ' ', last_name). In PostgreSQL, the || operator also returns NULL when encountering NULL.

Q: Q: What's the difference between ROUND and CEIL/FLOOR?

A: ROUND rounds to a specified number of decimal places, CEIL rounds up to the nearest integer, and FLOOR rounds down to the nearest integer. For example, 3.5: ROUND(3.5) = 4, CEIL(3.1) = 4, FLOOR(3.9) = 3.

Q: Q: Are date functions very different across databases?

A: Yes, the differences are significant. MySQL uses NOW(), DATEDIFF(), DATE_FORMAT(); SQL Server uses GETDATE(), DATEDIFF(), FORMAT(); PostgreSQL uses NOW(), date subtraction, TO_CHAR(). Consult the corresponding documentation when developing across databases.

Q: Q: Should I use CAST or CONVERT?

A: Use CAST — it's the SQL standard and supported by all major databases. CONVERT has different syntax in MySQL and SQL Server, resulting in poorer compatibility.

🌍 Real-World Analogy

SQL is like a Swiss Army knife — beyond the "blade" (queries) and "saw" (aggregation) you've already learned, it has many other useful tools:

String functions = Text editor — concatenating, extracting, case conversion
Numeric functions = Calculator — rounding, flooring, absolute values
Date functions = Calendar — getting current date, calculating day differences, extracting year/month/day
Type conversion = Translator — converting between numbers, text, and dates

Mastering these functions allows you to do more data processing in SQL, reducing the need for additional processing in the application layer.

🎯 Core Concepts

String Functions

CONCAT — String Concatenation

Joins multiple strings into one.

SQL

-- Concatenate name
SELECT CONCAT(first_name, ' ', last_name) AS full_name
FROM employees;

-- MySQL also supports || for concatenation (requires PIPES_AS_CONCAT to be enabled)
-- SELECT first_name || ' ' || last_name FROM employees;

SUBSTRING — Extract Substring

Extracts a portion of a string.

SQL

-- Extract the username part of an email (before @)
SELECT email, SUBSTRING(email, 1, POSITION('@' IN email) - 1) AS username
FROM employees
WHERE email IS NOT NULL;

-- Simple extraction: start from position 1, take 3 characters
SELECT SUBSTRING('Hello World', 1, 3);  -- Result: Hel

UPPER / LOWER — Case Conversion

SQL

SELECT UPPER('hello') AS uppercase, LOWER('WORLD') AS lowercase;
-- Result: HELLO, world

-- Practical use: case-insensitive search
SELECT * FROM employees WHERE LOWER(email) = 'zhangsan@company.com';

TRIM — Remove Spaces

SQL

-- Remove leading and trailing spaces
SELECT TRIM('  hello  ') AS trimmed;  -- Result: hello

-- Remove specified characters
SELECT TRIM(LEADING '0' FROM '0012300');  -- Result: 12300

REPLACE — Replace String

SQL

-- Mask the middle 4 digits of a phone number with ****
SELECT phone, REPLACE(phone, SUBSTRING(phone, 4, 4), '****') AS masked
FROM employees;

LENGTH — String Length

SQL

SELECT first_name, LENGTH(first_name) AS name_len
FROM employees;

Numeric Functions

ROUND — Rounding

SQL

SELECT ROUND(3.14159, 2);   -- Result: 3.14
SELECT ROUND(3.14159, 0);   -- Result: 3
SELECT ROUND(1234.5, -2);   -- Result: 1200 (round to hundreds)

CEIL / FLOOR — Round Up / Round Down

SQL

SELECT CEIL(3.1);    -- Result: 4 (round up)
SELECT CEIL(3.0);    -- Result: 3
SELECT FLOOR(3.9);   -- Result: 3 (round down)
SELECT FLOOR(3.0);   -- Result: 3

💡 Note: Function names differ across databases. SQL Server uses CEILING(), Oracle uses CEIL().

ABS — Absolute Value

SQL

SELECT ABS(-100);    -- Result: 100
SELECT ABS(50);      -- Result: 50

MOD — Modulo (Remainder)

SQL

SELECT MOD(10, 3);   -- Result: 1
SELECT MOD(100, 7);  -- Result: 2

-- SQL Server doesn't support the MOD function, use % instead
-- SELECT 10 % 3;    -- Result: 1

Date Functions

NOW — Current Date and Time

SQL

SELECT NOW();        -- Result: 2026-06-28 14:30:00 (current time)
SELECT CURDATE();    -- Result: 2026-06-28 (date only)
SELECT CURTIME();    -- Result: 14:30:00 (time only)

💡 Note: SQL Server uses GETDATE() instead of NOW().

DATE — Extract Date Part

SQL

SELECT DATE(NOW());  -- Result: 2026-06-28

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

DATEDIFF — Date Difference

SQL

-- Calculate the number of days between two dates
SELECT DATEDIFF('2026-12-31', '2026-01-01');  -- Result: 364

-- Calculate employee tenure in days
SELECT first_name, hire_date,
       DATEDIFF(CURDATE(), hire_date) AS tenure_days
FROM employees;

💡 Note: MySQL's DATEDIFF(date1, date2) calculates date1 - date2. SQL Server uses the same order. PostgreSQL uses date1 - date2 for direct subtraction.

DATE_FORMAT — Date Formatting

SQL

-- MySQL
SELECT DATE_FORMAT(order_date, '%Y-%m-%d') AS formatted_date
FROM orders;

-- SQL Server
-- SELECT FORMAT(order_date, 'yyyy-MM-dd') FROM orders;

EXTRACT — Extract Date Parts

SQL

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

Type Conversion

CAST — Type Conversion (Standard SQL)

SQL

-- String to integer
SELECT CAST('123' AS SIGNED INTEGER);  -- MySQL
SELECT CAST('123' AS INT);             -- SQL Server

-- Number to string
SELECT CAST(12345 AS CHAR);

-- Date to string
SELECT CAST(order_date AS CHAR) FROM orders;

-- String to date
SELECT CAST('2026-06-28' AS DATE);

CONVERT — Type Conversion (MySQL / SQL Server)

SQL

-- MySQL
SELECT CONVERT('123', SIGNED INTEGER);
SELECT CONVERT(123, CHAR);

-- SQL Server
SELECT CONVERT(INT, '123');
SELECT CONVERT(VARCHAR, 12345);

💡 Recommendation: CAST is the SQL standard and has better compatibility — it is recommended. CONVERT has significant syntax differences across databases.

📝 Basic Syntax

SQL

-- String functions
SELECT CONCAT(string1, string2) AS concatenated,
       SUBSTRING(string, start_position, length) AS extracted,
       UPPER(string) AS uppercase,
       LOWER(string) AS lowercase,
       TRIM(string) AS trimmed,
       REPLACE(string, old_value, new_value) AS replaced,
       LENGTH(string) AS length;

-- Numeric functions
SELECT ROUND(number, decimal_places) AS rounded,
       CEIL(number) AS rounded_up,
       FLOOR(number) AS rounded_down,
       ABS(number) AS absolute,
       MOD(number1, number2) AS remainder;

-- Date functions
SELECT NOW() AS current_time,
       CURDATE() AS current_date,
       DATEDIFF(date1, date2) AS day_difference,
       EXTRACT(YEAR FROM date) AS year;

-- Type conversion
SELECT CAST(value AS target_type) AS converted;

💡 Tip:

Function names and parameters may differ across databases — check the corresponding database documentation before use

CAST is the SQL standard and is recommended; CONVERT has different syntax in MySQL and SQL Server

Date functions have the most differences — pay special attention when developing across databases

📌 Examples

Example: String Processing — Employee Info Formatting

SQL

SELECT 
    CONCAT(UPPER(last_name), ', ', first_name) AS name,
    LOWER(email) AS email,
    SUBSTRING(phone, 1, 3) AS area_code,
    LENGTH(first_name) AS first_name_length,
    REPLACE(department_id, NULL, 'Unassigned') AS department
FROM employees
ORDER BY last_name;

▶ Try it Yourself

Output:

TEXT

name          email                   area_code  first_name_length  department
-----------  ----------------------  ---------  -----------------  ----------
LI, Si       lisi@company.com        138        2                  2
WANG, Wu     wangwu@company.com      139        2                  2
ZHANG, San   zhangsan@company.com    137        2                  1

💡 Interpretation: CONCAT + UPPER formats the name, LOWER standardizes the email to lowercase, SUBSTRING extracts the first 3 digits of the phone number.

Example: Numeric Calculation — Order Amount Processing

SQL

SELECT 
    order_id,
    total_amount AS original_amount,
    ROUND(total_amount * 0.9, 2) AS discounted_amount,
    CEIL(total_amount / 1000) AS thousands_ceil,
    FLOOR(total_amount / 1000) AS thousands_floor,
    MOD(total_amount, 100) AS remainder
FROM orders
WHERE status = 'completed';

▶ Try it Yourself

Output:

TEXT

order_id  original_amount  discounted_amount  thousands_ceil  thousands_floor  remainder
--------  --------------  -----------------  --------------  ---------------  ---------
1         15000.00        13500.00           15              15               0.00
2         8500.00         7650.00            9               8                0.00
3         3200.00         2880.00            4               3                0.00

💡 Interpretation: ROUND calculates the price after a 10% discount, CEIL and FLOOR round up and down to the nearest thousand respectively, MOD calculates the remainder.

Example: Date Calculation + Type Conversion

SQL

SELECT 
    first_name AS name,
    hire_date AS hire_date,
    DATEDIFF(CURDATE(), hire_date) AS tenure_days,
    EXTRACT(YEAR FROM hire_date) AS hire_year,
    CONCAT(CAST(salary AS CHAR), ' yuan') AS salary_display,
    DATE_FORMAT(hire_date, '%Y-%m') AS hire_month
FROM employees
ORDER BY hire_date;

▶ Try it Yourself

Output:

TEXT

name     hire_date   tenure_days  hire_year  salary_display  hire_month
------   ----------  -----------  ---------  --------------  ----------
Zhang    2023-03-15  1199         2023       15000.00 yuan   2023-03
Li       2023-07-01  1089         2023       18000.00 yuan   2023-07
Wang     2024-01-10  899          2024       12000.00 yuan   2024-01

💡 Interpretation: DATEDIFF calculates tenure in days, EXTRACT extracts the year, CAST converts the salary to a string before concatenating the unit, DATE_FORMAT formats the date.

🎬 Practice Scenarios

Scenario 1: Data Cleaning — Standardizing Formats

Data in the database has inconsistent formats and needs to be cleaned for display.

SQL

SELECT 
    employee_id,
    CONCAT(UPPER(TRIM(last_name)), ', ', TRIM(first_name)) AS standard_name,
    LOWER(TRIM(email)) AS standard_email,
    REPLACE(REPLACE(phone, '-', ''), ' ', '') AS standard_phone,
    CAST(salary AS DECIMAL(10,2)) AS standard_salary
FROM employees
WHERE email IS NOT NULL;

💡 Approach: TRIM removes extra spaces, UPPER/LOWER standardizes case, REPLACE cleans special characters from phone numbers, CAST ensures consistent data types.

Scenario 2: Report Statistics — Time-Based Analysis

Analyze order data by quarter.

SQL

SELECT 
    EXTRACT(YEAR FROM order_date) AS year,
    CEIL(EXTRACT(MONTH FROM order_date) / 3.0) AS quarter,
    COUNT(*) AS order_count,
    ROUND(SUM(total_amount), 2) AS total_amount,
    ROUND(AVG(total_amount), 2) AS avg_order_amount,
    DATEDIFF(MAX(order_date), MIN(order_date)) AS span_days
FROM orders
WHERE status != 'cancelled'
GROUP BY EXTRACT(YEAR FROM order_date), CEIL(EXTRACT(MONTH FROM order_date) / 3.0)
ORDER BY year, quarter;

💡 Approach: CEIL(MONTH / 3.0) converts months to quarters (1-3→Q1, 4-6→Q2, etc.). DATEDIFF calculates the time span for each quarter.

❓ FAQ

Q: What happens when CONCAT encounters NULL? A: In MySQL, CONCAT returns NULL when it encounters NULL. To treat NULL as an empty string, use COALESCE: CONCAT(COALESCE(first_name, ''), ' ', last_name). In PostgreSQL, the || operator also returns NULL when encountering NULL.

Q: What's the difference between ROUND and CEIL/FLOOR? A: ROUND rounds to a specified number of decimal places, CEIL rounds up to the nearest integer, and FLOOR rounds down to the nearest integer. For example, 3.5: ROUND(3.5) = 4, CEIL(3.1) = 4, FLOOR(3.9) = 3.

Q: Are date functions very different across databases? A: Yes, the differences are significant. MySQL uses NOW(), DATEDIFF(), DATE_FORMAT(); SQL Server uses GETDATE(), DATEDIFF(), FORMAT(); PostgreSQL uses NOW(), date subtraction, TO_CHAR(). Consult the corresponding documentation when developing across databases.

Q: Should I use CAST or CONVERT? A: Use CAST — it's the SQL standard and supported by all major databases. CONVERT has different syntax in MySQL and SQL Server, resulting in poorer compatibility.

📖 Summary

Category	Common Functions	Purpose
String	CONCAT, SUBSTRING, UPPER, LOWER, TRIM, REPLACE, LENGTH	Concatenation, extraction, conversion, cleaning
Numeric	ROUND, CEIL, FLOOR, ABS, MOD	Rounding, flooring, modulo
Date	NOW, CURDATE, DATEDIFF, EXTRACT, DATE_FORMAT	Get time, calculate differences, extract parts
Type Conversion	CAST, CONVERT	Data type conversion

String functions are used for data cleaning and formatting
Numeric functions are used for mathematical calculations and precision control
Date functions are used for time calculations and report statistics
CAST is the standard SQL type conversion function and is recommended
Function names and syntax may differ across databases — be aware when developing across databases

📝 Exercises

Exercise 1 (⭐): Query the employees table, concatenate first_name and last_name into a full name (uppercase), and calculate each person's tenure in days.

Exercise 2 (⭐⭐): Query the orders table, aggregate order count and total amount by month, using EXTRACT to extract year and month, and ROUND to keep 2 decimal places.

Exercise 3 (⭐⭐⭐): Query the employees table to generate an "employee business card" with the format: [Department] Name (Hire Year) - Monthly Salary: xxxxx yuan. Use string functions for concatenation and CAST to convert numbers to strings.

Next Lesson

👉 16-case-when - Conditional Expressions: Learn CASE WHEN conditional expressions and master conditional logic in SQL!

Web-Tutorial Tech Team

A team of developers maintaining programming tutorials. Each tutorial is written and reviewed by developers with expertise in that field. We work to keep our content accurate and reliable — if you spot an issue, please let us know.

About Us

Advanced Functions

Advanced Functions

🌍 Real-World Analogy

🎯 Core Concepts

String Functions

CONCAT — String Concatenation

SUBSTRING — Extract Substring

UPPER / LOWER — Case Conversion

TRIM — Remove Spaces

REPLACE — Replace String

LENGTH — String Length

Numeric Functions

ROUND — Rounding

CEIL / FLOOR — Round Up / Round Down

ABS — Absolute Value

MOD — Modulo (Remainder)

Date Functions

NOW — Current Date and Time

DATE — Extract Date Part

DATEDIFF — Date Difference

DATE_FORMAT — Date Formatting

EXTRACT — Extract Date Parts

Type Conversion

CAST — Type Conversion (Standard SQL)

CONVERT — Type Conversion (MySQL / SQL Server)

📝 Basic Syntax

📌 Examples

Example: String Processing — Employee Info Formatting

Example: Numeric Calculation — Order Amount Processing

Example: Date Calculation + Type Conversion

🎬 Practice Scenarios

Scenario 1: Data Cleaning — Standardizing Formats

Scenario 2: Report Statistics — Time-Based Analysis

❓ FAQ

📖 Summary

📝 Exercises

Next Lesson

🙏 帮我们做得更好