Skip to main content

SQL functions

In SQL, a function is a stored procedure that you can use and reuse over and over again. Functions are subprograms that take inputs, process them, and return a result. SQL functions can be used to perform calculations on data, to modify individual data items, to manipulate the output, or to format dates and numbers.

SQL functions are typically used in the SELECT statement. You can also use them in the WHERE clause of a query. The most common types of functions include aggregate functions and scalar functions.

Aggregate Functions​

Aggregate functions perform a calculation on a set of values and return a single value. Aggregate functions ignore NULL values in the column. Some of the most commonly used aggregate functions include:

  • COUNT(): Returns the number of rows that matches a specified criterion.
  • AVG(): Returns the average value of a numeric column.
  • SUM(): Returns the total sum of a numeric column.
  • MIN(): Returns the smallest value of the selected column.
  • MAX(): Returns the largest value of the selected column.

Example usage:

SELECT COUNT(column_name) FROM table_name;

Scalar Functions​

Scalar functions return a single value based on the input value. They operate on a single row and return a single result per row. Commonly used scalar functions include:

  • UCASE(): Converts a field to upper case.
  • LCASE(): Converts a field to lower case.
  • ROUND(): Rounds a numeric field to the number of decimals specified.
  • NOW(): Returns the current system date and time.

Example usage:

SELECT UCASE(column_name) FROM table_name;

SQL Functions based on Data Types​

SQL functions can also be categorized based on the data types they operate on. For example, there are specific functions for handling strings, dates, and numbers.

String Functions​

  • LENGTH(): Returns the length of a string.
  • TRIM(): Removes trailing and leading spaces from a string.
  • SUBSTRING(): Extracts a substring from a string.

Date Functions​

  • CURRENT_DATE: Returns the current date.
  • EXTRACT(): Extracts a part of a date (like year, month, day, etc.)
  • DATE_PART(): Similar to EXTRACT(), it returns a part of the date.

Numeric Functions​

  • ABS(): Returns the absolute value.
  • FLOOR(): Rounds a number down to the nearest integer.
  • CEIL(): Rounds a number up to the nearest integer.

User-Defined Functions​

In SQL, you can define your own functions. These are known as User-Defined Functions (UDFs). UDFs allows you to create custom functions according to your requirements.

Here is a basic syntax for creating a UDF:

CREATE FUNCTION function_name ([parameter ...]) 
  RETURNS return_datatype 
  LANGUAGE plpgsql 
  AS $$ 
    BEGIN 
      -- function_body 
    END; 
  $$;

Conclusion​

In this guide, we have introduced SQL functions, which are a powerful feature of SQL. Functions can help you to perform complex calculations, manipulate data, and format the output. Understanding how to use SQL functions effectively can greatly enhance your SQL programming capabilities.