SQL Built-in Functions

Overview of SQL Built-in Functions

SQL provides a rich set of built-in functions categorized by their purpose. These functions can be used in SELECT statements, WHERE clauses, and more.

Understanding these functions helps you write concise and powerful queries.

• String functions: manipulate text data.
• Numeric functions: perform mathematical operations.
• Date/time functions: handle date and time values.
• Aggregate functions: summarize data across rows.

String Functions

String functions allow you to manipulate text data by changing case, extracting substrings, trimming spaces, and more.

• UPPER() and LOWER() convert text to uppercase or lowercase.
• SUBSTRING() extracts a portion of a string.
• LENGTH() returns the length of a string.
• TRIM() removes leading and trailing spaces.

Numeric Functions

Numeric functions perform calculations such as rounding, absolute value, and finding the greatest or least values.

• ROUND() rounds a number to a specified decimal place.
• ABS() returns the absolute value of a number.
• CEIL() and FLOOR() round numbers up or down.
• POWER() raises a number to a power.

Date and Time Functions

Date/time functions help you extract parts of dates, calculate intervals, and format date values.

• NOW() returns the current date and time.
• DATEPART() extracts a specific part of a date (year, month, day).
• DATEDIFF() calculates the difference between two dates.
• DATE_FORMAT() formats date values into readable strings.

Aggregate Functions

Aggregate functions summarize data across multiple rows, often used with GROUP BY clauses.

• COUNT() counts rows or non-null values.
• SUM() adds numeric values.
• AVG() calculates the average.
• MIN() and MAX() find minimum and maximum values.