SQL System Functions

System functions are built-in functions that perform operations on one or more arguments and return a single value. They are categorized based on the type of operation they perform, such as string manipulation, date and time operations, mathematical calculations, and more.

String Functions

LEN()

Returns the length of a string.

LEN(string_expression)

SELECT LEN('Hello World');

UPPER()

Converts a string to uppercase.

UPPER(string_expression)

SELECT UPPER('sql server');

LOWER()

Converts a string to lowercase.

LOWER(string_expression)

SELECT LOWER('SQL SERVER');

SUBSTRING()

Extracts a part of a string.

SUBSTRING(string_expression, start_position, length)

SELECT SUBSTRING('Database', 5, 4);

REPLACE()

Replaces all occurrences of a substring with another substring.

REPLACE(string_expression, from_string, to_string)

SELECT REPLACE('Old String', 'Old', 'New');

Date and Time Functions

GETDATE()

Returns the current database system date and time.

GETDATE()

SELECT GETDATE();

DATEADD()

Adds a specified time interval to a date.

DATEADD(datepart, number, date)

SELECT DATEADD(day, 10, '2023-10-26');

DATEDIFF()

Returns the difference between two dates.

DATEDIFF(datepart, startdate, enddate)

SELECT DATEDIFF(month, '2023-01-15', '2023-07-20');

YEAR()

Returns the year part of a date.

YEAR(date)

SELECT YEAR('2023-11-05');

MONTH()

Returns the month part of a date.

MONTH(date)

SELECT MONTH('2023-11-05');

DAY()

Returns the day part of a date.

DAY(date)

SELECT DAY('2023-11-05');

Mathematical Functions

ABS()

Returns the absolute value of a number.

ABS(numeric_expression)

SELECT ABS(-10.5);

ROUND()

Rounds a numeric value to a specified length or precision.

ROUND(numeric_expression, length[, function])

SELECT ROUND(123.456, 2);

CEILING()

Returns the smallest integer greater than or equal to a specified numeric expression.

CEILING(numeric_expression)

SELECT CEILING(3.14);

FLOOR()

Returns the largest integer less than or equal to a specified numeric expression.

FLOOR(numeric_expression)

SELECT FLOOR(3.14);

Aggregate Functions

COUNT()

Returns the number of items in a specified group.

COUNT(expression)

SELECT COUNT(*) FROM Customers;

SUM()

Returns the sum of all the values in a numeric column.

SUM(expression)

SELECT SUM(Amount) FROM Orders;

AVG()

Returns the average value of a numeric column.

AVG(expression)

SELECT AVG(Price) FROM Products;

MAX()

Returns the largest value in a column.

MAX(expression)

SELECT MAX(Salary) FROM Employees;

MIN()

Returns the smallest value in a column.

MIN(expression)

SELECT MIN(OrderDate) FROM Orders;

Note: The specific syntax and available functions may vary slightly depending on the SQL dialect (e.g., SQL Server, MySQL, PostgreSQL, Oracle).

Caution: Using system functions incorrectly can lead to unexpected results or performance issues. Always refer to the specific documentation for your database system.