SQL | Advanced Functions

SQL supports a plethora of advanced functions that cater to different types of operations ranging from string manipulations to mathematical calculations and from date-time manipulations to analytical operations.

Here's a rundown of some advanced SQL functions across different categories:

String Functions:
- CONCAT: Combines multiple strings into one.
- SUBSTRING or SUBSTR: Extracts characters from a string.
- CHARINDEX or INSTR: Returns the position of a substring.
- REPLACE: Replaces occurrences of a substring.
- UPPER and LOWER: Convert string case.
- LTRIM and RTRIM: Removes leading or trailing spaces.
- REVERSE: Reverses a string.
Mathematical Functions:
- ROUND: Rounds a number.
- FLOOR and CEIL or CEILING: Rounds down or up to the nearest integer.
- ABS: Returns the absolute value.
- POWER: Raises a number to the power of another.
- SQRT: Computes the square root.
- LOG and EXP: Natural logarithm and exponential functions.
Date and Time Functions:
- GETDATE or SYSDATE: Gets the current date and time.
- DATEADD or ADD_MONTHS: Adds a time interval to a date.
- DATEDIFF: Calculates the difference between two dates.
- MONTH, YEAR, DAY: Extract parts of a date.
- TO_DATE and STR_TO_DATE: Convert string to date.
Conversion Functions:
- CAST: Converts a value from one type to another.
- CONVERT: Similar to CAST but sometimes offers more functionality, especially in SQL Server.
- TO_CHAR, TO_NUMBER: Converts data to character or number respectively (common in Oracle).
Aggregate Functions:
- AVG: Calculates the average of a set.
- SUM: Computes the sum of a set.
- MIN and MAX: Gets the minimum or maximum value.
- COUNT: Counts elements in a set.
- STDDEV and VARIANCE: Compute standard deviation and variance.
Analytical and Window Functions (often used in conjunction with the OVER clause):
- ROW_NUMBER(): Assigns a unique sequential integer to rows.
- RANK(): Assigns a rank to each row.
- DENSE_RANK(): Similar to RANK but without gaps.
- NTILE(n): Divides result set into ��n�� number of roughly equal parts.
- LEAD and LAG: Access data from subsequent or preceding rows.
- FIRST_VALUE and LAST_VALUE: Get the first or last value from a result set.
- CUME_DIST: Cumulative distribution of values.
Conditional Functions:
- CASE: Conditional logic in SQL.
- COALESCE: Returns the first non-null value from a list.
- NULLIF: Returns null if two values are the same, otherwise returns the first value.
- NVL (mostly in Oracle): Replaces null with a specified replacement value.

Remember, the availability and exact syntax of these functions might vary slightly based on the database system you're using. It's always a good practice to consult the official documentation of the specific SQL database you're working with.