SQL Functions


Check out our collection of SQL Functions below. For more information on any of the functions, just click on the function name!

Categories

Aggregate Functions


Objective

Function

Preview


Calculate the average value of a specified numeric column

AVG 🔗

AVG(column_name)

Calculate the standard deviation of values in a specified numeric column

STDDEV 🔗

STDDEV(column_name)

Calculate the total sum of a numeric column

SUM 🔗

SUM(column_name)

VARIANCE(column_name)

String Functions


Objective

Function

Preview


ASCII(character)

CHAR(ascii_code)

Return the number of characters in a string (length of the string)

CHAR_LENGTH(string)

CHARINDEX(substring, string, [start_position])

CONCAT 🔗

Concatenate two or more strings together

CONCAT(string1, string2, …)

DIFFERENCE(string1, string2)

LCASE(string)

LEFT(string, number_of_characters)

LENGTH 🔗

Return the number of characters in a string (length of the string)

LENGTH(string)

LOCATE(substring, string, [start_position])

LOWER(string)

POSITION(substring IN string)

REPLACE(string, old_substring, new_substring)

RIGHT 🔗

Extract a specified number of characters from the right side of a string

RIGHT(string, number_of_characters)

SPACE 🔗

Return a string of spaces of a specified length

SPACE(number_of_spaces)

TRIM 🔗

Remove leading and trailing spaces from a string

TRIM(string)

UCASE(string)

UPPER(string)

Numeric Functions


SECOND 🔗

Return the second component (0-59) of a time or datetime value

Objective

Function

Preview


ABS 🔗

Return the absolute value of a numeric expression

ABS(number)

CEIL 🔗

Return the smallest integer greater than or equal to a given number

CEIL(number)

EXP 🔗

Calculate the exponential value of a number

EXP(number)

FLOOR 🔗

Return the largest integer less than or equal to a given number

FLOOR(number)

LN 🔗

Calculate the natural logarithm (base e) of a number

LN(number)

LOG 🔗

Calculate the logarithm of a number with a specified base

Count the number of rows that are being returned

COUNT 🔗

COUNT(column_name)

Return the highest value in a specified column

MAX 🔗

MAX(column_name)

Return the lowest value in a specified column

MIN 🔗

MIN(column_name)

LOG(number)

LOG10 🔗

Calculate the base-10 logarithm of a number

LOG10(number)

MOD 🔗

Calculate the remainder of a division operation

MOD(dividend, divisor)

PI 🔗

Return the mathematical constant Pi

PI()

POWER 🔗

Raise a number to the power of another number

POWER(base, exponent)

RADIANS 🔗

Convert an angle from degrees to radians

RADIANS(angle)

RAND 🔗

Generate a random number between 0 and 1

RAND()

ROUND 🔗

Round a number to a specified number of decimal places

ROUND(number, decimal_places)

SIGN 🔗

Return the sign of a numeric expression (-1 for negative, 0 for zero, 1 for positive)

SIGN(number)

SQRT 🔗

Calculate the square root of a number

Date and Time Functions


Objective

Function

Preview

ADDDATE(date, INTERVAL number_of_days DAY)

ADDTIME 🔗

Add a specified time interval to a given time value

ADDTIME(time_value, time_interval)

CURDATE 🔗

Return the current date

CURDATE()

CURTIME 🔗

Return the current time

NTILE 🔗

Divide the result set into a specified number of approximately equal groups (tiles), assigning a bucket number to each row

Calculate the variance of values in a specified numeric column

VARIANCE 🔗

Return the ASCII code of the first character in a string

ASCII 🔗

Return the character based on the ASCII code provided as an argument

CHAR 🔗

Return the starting position of a substring within a string

CHARINDEX 🔗

Compute the difference value between two strings based on their SOUNDEX values

DIFFERENCE 🔗

Convert a string to lowercase

LCASE 🔗

Extract a specified number of characters from the left side of a string

LEFT 🔗

Find the starting position of a substring within a string

LOCATE 🔗

Convert a string to lowercase

LOWER 🔗

Return the position of the first occurrence of a substring within a string

POSITION 🔗

Replace all occurrences of a substring within a string with another substring

REPLACE 🔗

Convert a string to uppercase

UCASE 🔗

Convert a string to uppercase

UPPER 🔗

SQRT(number)


Add a specified number of days to a date

ADDDATE 🔗

CURTIME()

Add a specified time interval to a date or datetime value

DATE_ADD 🔗

DATE_ADD(date, INTERVAL expression unit)

Format a date or datetime value based on a specified format string

DATE_FORMAT(date, format_string)

Subtract a specified time interval from a date or datetime value

DATE_SUB 🔗

DATE_SUB(date, INTERVAL expression unit)

Calculate the difference between two dates in terms of a specified unit (e.g., days, months, years)

DATEDIFF 🔗

DATEDIFF(end_date, start_date)

Extract the day of the month from a date or datetime value

DAY 🔗

DAY(date)

Return the name of the day of the week for a given date

DAYNAME 🔗

DAYNAME(date)

Return the day of the month (1-31) for a given date

DAYOFMONTH 🔗

DAYOFMONTH(date)

Return the day of the week as a number (1 for Sunday, 2 for Monday, ..., 7 for Saturday) for a given date

DAYOFWEEK 🔗

DAYOFWEEK(date)

Return the day of the year (1-366) for a given date

DAYOFYEAR 🔗

DAYOFYEAR(date)

Return the hour component (0-23) of a time or datetime value

HOUR 🔗

HOUR(time)

MINUTE 🔗

Return the minute component (0-59) of a time or datetime value

MINUTE(time)

Return the month component (1-12) of a date or datetime value

MONTH 🔗

MONTH(time)

SECOND(time)

YEAR(date)

Ranking Functions


Function

Objective

Preview


DENSE_RANK 🔗

Assign a rank to each row within a result set, where rows with equal values get the same rank, and no gaps in the ranking sequence are allowed

No Preview Available - Too Long

Return the value of the specified expression from the first row of the window frame defined by the window function

No Preview Available - Too Long

LAG 🔗

Retrieve the value from a previous row within the result set, based on a specified column or expression

No Preview Available - Too Long

LAST_VALUE 🔗

Return the value of the specified expression from the last row of the window frame defined by the window function

No Preview Available - Too Long

LEAD 🔗

Retrieve the value from a subsequent row within the result set, based on a specified column or expression

No Preview Available - Too Long

No Preview Available - Too Long

Calculate the relative rank of each row within a result set, expressed as a percentage

No Preview Available - Too Long

RANK 🔗

Assign a unique rank to each row within a result set based on a specified column or expression

No Preview Available - Too Long

ROW_NUMBER 🔗

Assign a unique sequential row number to each row within a result set

No Preview Available - Too Long

Conditional Functions


Function

Objective

Return the year component of a date or datetime value

YEAR 🔗

Preview


Perform conditional logic in SQL, similar to if-then-else statements in programming languages

CASE 🔗

No Preview Available - Too Long

Return the first non-null expression in a list of expressions

COALESCE 🔗

COALESCE(expr1, expr2, …)

Conditional transformation of values, primarily used in Oracle SQL

DECODE 🔗

DECODE(expression, search_value1, result_1, …, default_result)

Conditionally return a value based on a boolean expression

IF 🔗

IF(condition, true_value, false_value)

Return the second value if the first value is null

IFNULL 🔗

IFNULL(expr1, expr2)

Return one of two values depending on a boolean expression (SQL Server)

IIF 🔗

IIF(condition, true_value, false_value)

Return the replacement value if the expression is null (SQL Server)

ISNULL 🔗

ISNULL(expr, replacement_value)

Return null if two expressions are equal, otherwise return the first expression

NULLIF 🔗

NULLIF(expr1, expr2)

Conversion Functions


Function

Objective

Preview


Convert an expression from one data type to another in SQL

CAST 🔗

CAST(expression AS new_data_type)

Convert an expression from one data type to another in SQL (supports additional formatting)

CONVERT 🔗

CONVERT(new_data_type, expression, [style])

Convert a string representation of a date/time value to a datetime data type in SQL Server

PARSE 🔗

PARSE(expression AS data_type USING culture)

Safely attempt to convert an expression from one data type to another; returns null if conversion fails

TRY_CAST 🔗

TRY_CAST(expression AS new_data_type)

Safely attempt to convert an expression from one data type to another with additional formatting options; returns null if conversion fails

TRY_CONVERT(new_data_type, expression, [style])

Safely attempt to convert a string representation of a date/time value to a datetime data type in SQL Server; returns null if conversion fails

TRY_PARSE 🔗

TRY_PARSE(expression AS data_type USING culture)