SQL Functions Built-in Functions and User-Defined Functions

SQL Functions: Built-in Functions and User-Defined Functions

by
DatabaseSQL

SQL functions play a pivotal role in data manipulation and retrieval within databases. They allow for efficient and effective data processing by performing operations on the data stored in the database. SQL functions can be broadly categorized into two types: built-in functions and user-defined functions. This article explores both types, providing examples and highlighting their importance in SQL programming.

Built-in Functions

Built-in functions are pre-defined functions provided by SQL. They perform specific operations and return a single value. These functions are categorized into several groups:

  1. Aggregate Functions
  • SUM(): Calculates the total sum of a numeric column.
    sql
    SELECT SUM(salary) FROM employees;
  • AVG(): Computes the average value of a numeric column.
    sql
    SELECT AVG(salary) FROM employees;
  • COUNT(): Returns the number of rows that match a specified condition.
    sql
    SELECT COUNT(*) FROM employees WHERE department = ‘Sales’;

Subscribe to our newsletter

Follow Us
Facebook Tiktok Instagram
  1. String Functions:
  • CONCAT(): Concatenates two or more strings.
    sql
    SELECT CONCAT(first_name, ‘ ‘, last_name) AS full_name FROM employees;
  • SUBSTRING(): Extracts a substring from a string.
    sql
    SELECT SUBSTRING(first_name, 1, 3) AS short_name FROM employees;
  • LENGTH(): Returns the length of a string.
    sql
    SELECT LENGTH(first_name) FROM employees;
  1. Date and Time Functions:
  • CURRENT_DATE(): Returns the current date.
    sql
    SELECT CURRENT_DATE();
  • DATEDIFF(): Calculates the difference between two dates.
    sql
    SELECT DATEDIFF(NOW(), hire_date) AS days_worked FROM employees;
  • DATE_FORMAT(): Formats a date value according to a specified format.
    sql
    SELECT DATE_FORMAT(hire_date, ‘%Y-%m-%d’) AS formatted_date FROM employees;
  1. Numeric Functions:
  • ROUND(): Rounds a number to a specified number of decimal places.
    sql
    SELECT ROUND(salary, 2) FROM employees;
  • CEIL(): Returns the smallest integer greater than or equal to a number.
    sql
    SELECT CEIL(salary) FROM employees;
  • FLOOR(): Returns the largest integer less than or equal to a number.
    sql
    SELECT FLOOR(salary) FROM employees;

User-Defined Functions (UDFs)

User-defined functions are custom functions created by users to perform specific tasks that are not covered by built-in functions. UDFs allow for more complex operations and can be tailored to meet unique requirements.

  1. Creating a User-Defined Function:
  • UDFs are created using the CREATE FUNCTION statement.
    sql
    CREATE FUNCTION get_full_name (first_name VARCHAR(50), last_name VARCHAR(50))
    RETURNS VARCHAR(100)
    BEGIN
    RETURN CONCAT(first_name, ‘ ‘, last_name);
    END;
  1. Using a User-Defined Function:
  • Once created, UDFs can be used in SQL queries just like built-in functions.
    sql
    SELECT get_full_name(first_name, last_name) AS full_name FROM employees;
  1. Advantages of UDFs:
  • Reusability: UDFs can be reused in multiple queries, reducing redundancy.
  • Modularity: UDFs help in breaking down complex queries into simpler, modular components.
  • Maintainability: Changes can be made in one place without altering the queries that use the function.
  1. Example of a UDF:
  • A UDF to calculate the age of an employee based on their birth date.
    sql
    CREATE FUNCTION calculate_age (birth_date DATE)
    RETURNS INT
    BEGIN
    RETURN YEAR(CURDATE()) – YEAR(birth_date);
    END; Using the UDF:
    sql
    SELECT first_name, last_name, calculate_age(birth_date) AS age FROM employees;

Conclusion

SQL functions, both built-in and user-defined, are essential tools for database manipulation and management. Built-in functions provide ready-to-use operations for common tasks, while user-defined functions offer the flexibility to create customized solutions tailored to specific needs. Understanding and effectively using these functions can greatly enhance the efficiency and effectiveness of SQL programming.

Add a Comment