Built-in Scalar Functions

This section documents standard SQL (ISO/IEC 9075-2 (SQL/Foundation)) functionality only. Vendor-specific extensions or behavior of any particular database management system are intentionally left out. In the US, the standard is also distributed via ANSI’s webstore (search "ISO/IEC 9075"). This content was generated with the assistance of AI and should be verified against your target DBMS’s own documentation before relying on it in production.

This page covers the standard SQL scalar functions — functions that take zero or more argument values and return a single value per invocation. Aggregate functions (COUNT, SUM, AVG, MIN, MAX) and window functions are covered separately in Aggregate and Window Functions. Functions that operate on JSON or XML values are covered separately in JSON and XML Functions. This page is scoped to the remaining families: string, numeric, trigonometric, date/time, and type-conversion functions. For the authoritative (if less readable) specification, see ISO/IEC 9075-2 (SQL/Foundation), the part of the standard that defines these scalar functions.

String functions

String functions operate on character string values (CHARACTER, VARCHAR, and similar types).

SUBSTRING

SUBSTRING extracts a portion of a character string, starting at a given position and optionally limited to a given length.

SUBSTRING(string FROM start [FOR length])

Parameters/options:

  • string — the source character string expression.

  • start — the 1-based position at which the extracted substring begins. Standard SQL numbers string positions starting at 1, not 0.

  • length — the optional maximum number of characters to extract. When omitted, extraction continues to the end of the string.

SELECT SUBSTRING(last_name FROM 1 FOR 3) AS name_prefix
FROM employees

CONCAT / ||

Standard SQL provides two ways to join character strings end to end: the || concatenation operator, and the CONCAT function.

string_1 || string_2
CONCAT(string_1, string_2)

Parameters/options:

  • string_1, string_2 — the character string expressions to join, in order. Both forms accept NULL operands, in which case the standard-defined result is NULL.

SELECT first_name || ' ' || last_name AS full_name
FROM employees
SELECT CONCAT(first_name, ' ', last_name) AS full_name
FROM employees

TRIM

TRIM removes leading and/or trailing occurrences of a character (a space, by default) from a string.

TRIM([LEADING | TRAILING | BOTH] [characters] FROM string)

Parameters/options:

  • LEADING — removes only occurrences at the start of the string.

  • TRAILING — removes only occurrences at the end of the string.

  • BOTH — removes occurrences at both the start and the end of the string. This is the default when no keyword is given.

  • characters — the single character to strip. When omitted, a space character is assumed.

  • string — the source character string expression.

SELECT TRIM(BOTH ' ' FROM '  Engineering  ') AS department

TRIM (LEADING / TRAILING)

The LEADING and TRAILING keywords restrict trimming to only one end of the string, leaving the other end untouched.

SELECT TRIM(LEADING '0' FROM '00042') AS trimmed_leading,
       TRIM(TRAILING '.' FROM 'v1.2.') AS trimmed_trailing

UPPER / LOWER

UPPER and LOWER fold every alphabetic character in a string to upper case or lower case, respectively.

UPPER(string)
LOWER(string)

Parameters/options:

  • string — the source character string expression.

SELECT UPPER(department) AS department_upper,
       LOWER(department) AS department_lower
FROM employees

OVERLAY

OVERLAY replaces a portion of a string with a replacement string, starting at a given position and optionally limited to a given length. It is the standard SQL function for substring replacement.

OVERLAY(string PLACING replacement FROM start [FOR length])

Parameters/options:

  • string — the source character string expression.

  • replacement — the character string to insert in place of the replaced portion.

  • start — the 1-based position at which replacement begins.

  • length — the optional number of characters of string to remove starting at start. When omitted, it defaults to the length of replacement.

SELECT OVERLAY(phone_number PLACING '***' FROM 1 FOR 3) AS masked_phone
FROM employees

CHAR_LENGTH / CHARACTER_LENGTH

CHAR_LENGTH (also written CHARACTER_LENGTH) returns the number of characters in a string. This is the standard SQL function name; the shorter LENGTH spelling seen in many products is a common but non-standard addition and is intentionally left out of this page per the disclaimer above.

CHAR_LENGTH(string)
CHARACTER_LENGTH(string)

Parameters/options:

  • string — the character string expression whose length is measured, counted in characters (not bytes).

SELECT last_name, CHAR_LENGTH(last_name) AS name_length
FROM employees

Numeric functions

Numeric functions operate on exact and approximate numeric values (INTEGER, DECIMAL, FLOAT, and similar types).

ABS

ABS returns the absolute (non-negative) value of a numeric expression.

ABS(numeric_expression)

Parameters/options:

  • numeric_expression — the numeric value whose magnitude is returned, discarding its sign.

SELECT ABS(balance - target_balance) AS balance_difference
FROM accounts

ROUND

ROUND rounds a numeric value to a given number of decimal places.

ROUND(numeric_expression [, precision])

Parameters/options:

  • numeric_expression — the numeric value to round.

  • precision — the optional number of digits to keep to the right of the decimal point. A negative value rounds to the left of the decimal point (for example, to the nearest 10 or 100). When omitted, precision defaults to 0, rounding to the nearest whole number.

SELECT ROUND(salary, -3) AS salary_rounded_to_thousand
FROM employees

CEIL / CEILING

CEIL (also written CEILING) rounds a numeric value up to the smallest integer greater than or equal to it.

CEIL(numeric_expression)
CEILING(numeric_expression)

Parameters/options:

  • numeric_expression — the numeric value to round up.

SELECT price, CEIL(price) AS price_rounded_up
FROM products

FLOOR

FLOOR rounds a numeric value down to the largest integer less than or equal to it.

FLOOR(numeric_expression)

Parameters/options:

  • numeric_expression — the numeric value to round down.

SELECT price, FLOOR(price) AS price_rounded_down
FROM products

MOD

MOD returns the remainder of dividing one numeric value by another.

MOD(dividend, divisor)

Parameters/options:

  • dividend — the numeric value being divided.

  • divisor — the numeric value to divide by. The sign of the result follows the sign of dividend.

SELECT employee_id, MOD(employee_id, 2) AS is_odd_id
FROM employees

SQRT

SQRT returns the (principal, non-negative) square root of a numeric expression. It was added to the standard as part of SQL:2008’s optional Feature T621, "Enhanced numeric functions" — most mainstream databases implement it under this same standard name.

SQRT(numeric_expression)

Parameters/options:

  • numeric_expression — the numeric value whose square root is computed. The value must be non-negative; behavior for a negative argument is implementation-defined.

SELECT SQRT(variance) AS standard_deviation
FROM statistics

POWER

POWER raises a numeric value to a given exponent. Also part of SQL:2008’s Feature T621. Note the standard spells this POWER, not the shorter POW seen in some products.

POWER(base, exponent)

Parameters/options:

  • base — the numeric value to raise to a power.

  • exponent — the numeric exponent to raise base to.

SELECT POWER(radius, 2) * 3.14159 AS circle_area
FROM circles

EXP

EXP returns the natural exponential of a numeric value, that is, e raised to the given power. Part of SQL:2008’s Feature T621.

EXP(numeric_expression)

Parameters/options:

  • numeric_expression — the exponent to raise the mathematical constant e to.

SELECT EXP(growth_rate) AS growth_factor
FROM investments

LN

LN returns the natural logarithm (base e) of a numeric value. Part of SQL:2008’s Feature T621.

LN(numeric_expression)

Parameters/options:

  • numeric_expression — the numeric value to take the natural logarithm of. Must be positive.

SELECT LN(population) AS log_population
FROM cities

LOG10

LOG10 returns the base-10 (common) logarithm of a numeric value. Part of SQL:2008’s Feature T621.

LOG10(numeric_expression)

Parameters/options:

  • numeric_expression — the numeric value to take the base-10 logarithm of. Must be positive.

SELECT LOG10(byte_count) AS order_of_magnitude
FROM files

Trigonometric functions

Standard SQL defines a family of trigonometric functions as SQL:2008’s optional Feature T622, "Trigonometric functions". As with SQRT/POWER/EXP/LN/LOG10 above, a database need not implement these to claim SQL:2008+ conformance, but most mainstream products do, under these same standard names.

SIN / COS / TAN

SIN, COS, and TAN return the sine, cosine, and tangent of a numeric expression, taken as an angle in radians.

SIN(numeric_expression)
COS(numeric_expression)
TAN(numeric_expression)

Parameters/options:

  • numeric_expression — the angle, in radians, to evaluate.

SELECT angle_radians,
       SIN(angle_radians) AS sine,
       COS(angle_radians) AS cosine
FROM angles

ASIN / ACOS / ATAN

ASIN, ACOS, and ATAN return the inverse sine, inverse cosine, and inverse tangent of a numeric expression, expressed as an angle in radians.

ASIN(numeric_expression)
ACOS(numeric_expression)
ATAN(numeric_expression)

Parameters/options:

  • numeric_expression — for ASIN/ACOS, a value in the range [-1, 1]; for ATAN, any numeric value. The result is expressed in radians.

SELECT ratio, ATAN(ratio) AS angle_radians
FROM slopes

SINH / COSH / TANH

SINH, COSH, and TANH return the hyperbolic sine, hyperbolic cosine, and hyperbolic tangent of a numeric expression.

SINH(numeric_expression)
COSH(numeric_expression)
TANH(numeric_expression)

Parameters/options:

  • numeric_expression — the numeric value to evaluate.

SELECT x, SINH(x) AS hyperbolic_sine
FROM sample_values

Date/time functions

Date/time functions return or operate on values of the DATE, TIME, and TIMESTAMP data types.

CURRENT_DATE

CURRENT_DATE returns the current date, as determined by the SQL session, with no time component.

CURRENT_DATE

Parameters/options:

  • Takes no arguments and no parentheses — it is a niladic (parameterless) function, evaluated once per statement execution.

SELECT order_id, order_date
FROM orders
WHERE order_date = CURRENT_DATE

CURRENT_TIME

CURRENT_TIME returns the current time of day, without a date component, in the session’s time zone.

CURRENT_TIME[(precision)]

Parameters/options:

  • precision — the optional number of fractional-second digits to include. When omitted, an implementation-defined default precision is used.

SELECT CURRENT_TIME(0) AS time_now

CURRENT_TIMESTAMP

CURRENT_TIMESTAMP returns the current date and time together, in the session’s time zone.

CURRENT_TIMESTAMP[(precision)]

Parameters/options:

  • precision — the optional number of fractional-second digits to include. When omitted, an implementation-defined default precision is used.

INSERT INTO audit_log (event_name, logged_at)
VALUES ('order_created', CURRENT_TIMESTAMP)

LOCALTIME

LOCALTIME returns the current time of day in the session’s local time zone, like CURRENT_TIME, but as a TIME WITHOUT TIME ZONE value — it never carries a time zone offset, even when the session’s time zone is not UTC.

LOCALTIME[(precision)]

Parameters/options:

  • precision — the optional number of fractional-second digits to include. When omitted, an implementation-defined default precision is used.

SELECT LOCALTIME(0) AS local_time_now

LOCALTIMESTAMP

LOCALTIMESTAMP returns the current date and time in the session’s local time zone, like CURRENT_TIMESTAMP, but as a TIMESTAMP WITHOUT TIME ZONE value — it never carries a time zone offset.

LOCALTIMESTAMP[(precision)]

Parameters/options:

  • precision — the optional number of fractional-second digits to include. When omitted, an implementation-defined default precision is used.

INSERT INTO audit_log (event_name, logged_at)
VALUES ('order_created', LOCALTIMESTAMP)

EXTRACT

EXTRACT pulls a single field — such as the year, month, or hour — out of a date, time, or timestamp value.

EXTRACT(field FROM source)

Parameters/options:

  • field — the component to extract. Standard fields include YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND.

  • source — the DATE, TIME, or TIMESTAMP expression to extract the field from. Not every field is valid for every source type — for example, HOUR cannot be extracted from a plain DATE value.

SELECT order_id,
       EXTRACT(YEAR FROM order_date) AS order_year,
       EXTRACT(MONTH FROM order_date) AS order_month,
       EXTRACT(DAY FROM order_date) AS order_day
FROM orders

EXTRACT (time fields)

The same EXTRACT function accepts time-based fields when its source expression carries a time component.

SELECT event_name,
       EXTRACT(HOUR FROM logged_at) AS event_hour
FROM audit_log

Type-conversion functions

Type-conversion functions convert a value from one data type to another.

CAST

CAST converts an expression from its current data type to a specified target data type.

CAST(expression AS target_type)

Parameters/options:

  • expression — the value to convert. It may be a column reference, a literal, or any other valid expression.

  • target_type — the SQL data type to convert expression into (for example, INTEGER, VARCHAR(n), DECIMAL(p, s), or DATE). The conversion fails at run time if the source value cannot be represented in the target type.

SELECT CAST(employee_id AS VARCHAR(10)) AS employee_id_text,
       CAST('2024-01-15' AS DATE) AS parsed_date
FROM employees