SQL 函数参考

Returns the number of characters in a string.

SELECT LENGTH('hello') -- 5

Converts all characters in a string to uppercase.

SELECT UPPER('hello') -- 'HELLO'

Converts all characters in a string to lowercase.

SELECT LOWER('HELLO') -- 'hello'

Extracts a substring starting at position start (1-indexed). Optional length limits characters returned.

SELECT SUBSTRING('hello world' FROM 7 FOR 5) -- 'world'

Concatenates two or more strings together.

SELECT CONCAT('hello', ' ', 'world') -- 'hello world'

Concatenates strings with a separator, skipping NULL values.

SELECT CONCAT_WS(', ', 'Alice', NULL, 'Bob') -- 'Alice, Bob'

Replaces all occurrences of a substring with another string.

SELECT REPLACE('hello world', 'world', 'SQL') -- 'hello SQL'

Removes leading and/or trailing characters (default: spaces) from a string.

SELECT TRIM('  hello  ') -- 'hello'

Returns the position of the first occurrence of a substring (1-indexed, 0 if not found).

SELECT INSTR('hello world', 'world') -- 7

Returns the leftmost n characters of a string.

SELECT LEFT('hello world', 5) -- 'hello'

Returns the rightmost n characters of a string.

SELECT RIGHT('hello world', 5) -- 'world'

Returns a string repeated count times.

SELECT REPEAT('ab', 3) -- 'ababab'

Returns the string with characters in reverse order.

SELECT REVERSE('hello') -- 'olleh'

Pads a string on the left to a total length using the fill character.

SELECT LPAD('42', 5, '0') -- '00042'

Pads a string on the right to a total length using the fill character.

SELECT RPAD('hi', 5, '.') -- 'hi...'

Splits a string by delimiter and returns the nth field (1-indexed).

SELECT SPLIT_PART('a,b,c', ',', 2) -- 'b'

Replaces substrings matching a regular expression with a replacement string.

SELECT REGEXP_REPLACE('abc123', '[0-9]+', 'N') -- 'abcN'

Returns the current date and time (including timezone in PostgreSQL).

SELECT NOW() -- 2024-06-15 14:30:00

Returns the current date without a time component.

SELECT CURRENT_DATE -- 2024-06-15

Adds a time interval to a date or timestamp.

SELECT DATE_ADD('2024-01-01', INTERVAL 7 DAY) -- '2024-01-08'

Returns the number of days between two dates.

SELECT DATEDIFF('2024-01-31', '2024-01-01') -- 30

Extracts a specific date/time part (YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, WEEK, DOW).

SELECT EXTRACT(YEAR FROM NOW()) -- 2024

Truncates a timestamp to the specified precision (year, month, day, hour, minute, etc.).

SELECT DATE_TRUNC('month', NOW()) -- 2024-06-01 00:00:00

Formats a date, timestamp, or number as a string using a format pattern.

SELECT TO_CHAR(NOW(), 'YYYY-MM-DD') -- '2024-06-15'

Returns the interval between two timestamps expressed in years, months, and days.

SELECT AGE('2024-06-15', '2000-03-01') -- '24 years 3 months 14 days'

Returns the absolute (non-negative) value of a number.

SELECT ABS(-42) -- 42

Rounds a number to the specified number of decimal places (default 0).

SELECT ROUND(3.14159, 2) -- 3.14

Rounds a number up to the nearest integer.

SELECT CEIL(4.2) -- 5

Rounds a number down to the nearest integer.

SELECT FLOOR(4.9) -- 4

Returns the remainder after division.

SELECT MOD(10, 3) -- 1

Returns base raised to the power of exponent.

SELECT POWER(2, 10) -- 1024

Returns the square root of a non-negative number.

SELECT SQRT(16) -- 4

Returns the largest value from a list of expressions.

SELECT GREATEST(3, 1, 4, 1, 5) -- 5

Returns the smallest value from a list of expressions.

SELECT LEAST(3, 1, 4, 1, 5) -- 1

Returns a random floating-point value between 0.0 and 1.0.

SELECT RANDOM() -- 0.7381924...

Returns -1 for negative numbers, 0 for zero, and 1 for positive numbers.

SELECT SIGN(-42) -- -1

Counts rows. COUNT(*) counts all rows; COUNT(col) skips NULLs; DISTINCT counts unique non-null values.

SELECT COUNT(*) FROM users -- 150

Returns the sum of non-null numeric values.

SELECT SUM(amount) FROM orders -- 42000.00

Returns the arithmetic mean of non-null values.

SELECT AVG(score) FROM results -- 87.5

Returns the minimum non-null value in the group.

SELECT MIN(created_at) FROM orders -- 2023-01-01

Returns the maximum non-null value in the group.

SELECT MAX(score) FROM results -- 100

Concatenates non-null string values from a group with a delimiter.

SELECT STRING_AGG(name, ', ') FROM users -- 'Alice, Bob, Eve'

Collects values from a group into an array (PostgreSQL) or JSON array.

SELECT ARRAY_AGG(id ORDER BY id) FROM users -- {1,2,3}

Returns TRUE if all non-null input values are true.

SELECT BOOL_AND(active) FROM users -- false

Assigns a unique sequential integer to each row within a partition, starting at 1.

SELECT ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS rn FROM employees

Assigns a rank to each row; ties share the same rank and leave gaps in sequence.

SELECT RANK() OVER (ORDER BY score DESC) AS rnk FROM results

Like RANK but without gaps: tied rows share the same rank and the next rank is consecutive.

SELECT DENSE_RANK() OVER (ORDER BY score DESC) FROM results

Returns the value from a row that is offset rows before the current row.

SELECT LAG(revenue) OVER (ORDER BY month) AS prev_revenue FROM sales

Returns the value from a row that is offset rows after the current row.

SELECT LEAD(revenue) OVER (ORDER BY month) AS next_revenue FROM sales

Divides rows into n roughly equal groups (tiles) and returns the tile number for each row.

SELECT NTILE(4) OVER (ORDER BY score) AS quartile FROM results

Returns the value of expression from the first row of the window frame.

SELECT FIRST_VALUE(salary) OVER (PARTITION BY dept ORDER BY salary DESC) FROM employees

Returns the value from the last row of the window frame. Requires explicit ROWS BETWEEN clause for meaningful results.

SELECT LAST_VALUE(salary) OVER (PARTITION BY dept ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FROM employees

Returns the relative rank of a row as a fraction between 0 and 1.

SELECT PERCENT_RANK() OVER (ORDER BY score) FROM results

Returns the first non-null value from the list. Short-circuits once a non-null is found.

SELECT COALESCE(NULL, NULL, 'fallback') -- 'fallback'

Returns NULL if value1 equals value2; otherwise returns value1. Useful to avoid division-by-zero.

SELECT total / NULLIF(divisor, 0) FROM sales

Evaluates conditions in order and returns the result for the first true condition. Equivalent to if-else.

SELECT CASE WHEN score >= 90 THEN 'A' WHEN score >= 80 THEN 'B' ELSE 'C' END FROM results

Short form of CASE WHEN: returns true_value if condition is true, otherwise false_value.

SELECT IIF(active = 1, 'Active', 'Inactive') FROM users

Converts a value to the specified data type. Standard SQL syntax supported by all major databases.

SELECT CAST('42' AS INTEGER) -- 42

Converts a value to the specified type. Dialect varies: MySQL/SQL Server have CONVERT; PostgreSQL prefers CAST.

SELECT CONVERT(INT, '42') -- 42

Parses a string into a DATE using the specified format pattern.

SELECT TO_DATE('15-Jun-2024', 'DD-Mon-YYYY') -- 2024-06-15

Converts a formatted string to a numeric value.

SELECT TO_NUMBER('1,234.56', '9,999.99') -- 1234.56

Extracts a value from a JSON document at the specified path.

SELECT JSON_EXTRACT('{"name":"Alice"}', '$.name') -- "Alice"

PostgreSQL operator: extracts a JSON sub-object or array element. Use ->> to get a text value.

SELECT data -> 'address' -> 'city' FROM users

Creates a JSON object from key-value pairs.

SELECT JSON_OBJECT('name', 'Alice', 'age', 30) -- {"name":"Alice","age":30}

Creates a JSON array from the provided values.

SELECT JSON_ARRAY(1, 'two', true) -- [1,"two",true]

Aggregates values as a JSON array. Useful with GROUP BY to collect related rows.

SELECT dept, JSON_AGG(name) FROM employees GROUP BY dept

Expands a JSON object into a set of (key, value) rows. Used in FROM clause.

SELECT key, value FROM JSON_EACH('{"a":1,"b":2}')

PostgreSQL: creates a JSONB object from key-value pairs. Prefer over json_build_object when working with JSONB columns.

SELECT JSONB_BUILD_OBJECT('id', id, 'name', name) FROM users