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
Compute the difference value between two strings based on their SOUNDEX values
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(date)
Return the day of the week as a number (1 for Sunday, 2 for Monday, ..., 7 for Saturday) for a given date
DAYOFWEEK(date)
Return the day of the year (1-366) for a given date
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
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
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
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(expression AS data_type USING culture)