Aggregate Functions

   A scalar function in SQL is a type of function that operates on individual values, returning a single result for each input value. Unlike aggregate functions, which operate on sets of values and return a single result for the entire set, scalar functions work on each row independently. These functions can perform various operations such as string manipulation, mathematical calculations, date/time operations, and type conversion. Scalar functions are commonly used within SELECT statements, WHERE clauses, and expressions to transform or manipulate data on a row-by-row basis.

 

1. String Functions:
• CONCAT: Concatenates two or more strings.
• SUBSTRING: Extracts a substring from a string.
• UPPER/UCASE: Converts a string to uppercase.
• LOWER/LCASE: Converts a string to lowercase.
• TRIM: Removes leading and trailing spaces from a string.
• LENGTH/LEN: Returns the length of a string.

 

2. Numeric Functions:
• ABS: Returns the absolute value of a number.
• ROUND: Rounds a number to a specified number of decimal places.
• CEILING: Rounds a number up to the nearest integer.
• FLOOR: Rounds a number down to the nearest integer.
• SQRT: Returns the square root of a number.

 

3. Date and Time Functions:
• DATEADD: Adds a specified time interval to a date.
• DATEDIFF: Calculates the difference between two dates.
• DATEPART: Returns a specific part of a date (e.g., year, month, day).
• GETDATE/NOW: Returns the current date and time.
• DATE_FORMAT: Formats a date according to a specified format.

 

4. Conversion Functions:
• CAST/CONVERT: Converts a value from one data type to another.
• COALESCE: Returns the first non-null value in a list of expressions.

 

5. Mathematical Functions:
• SIN, COS, TAN: Trigonometric functions.
• LOG, LOG10, EXP: Logarithmic functions.

 

These functions can be used in SQL queries to perform various operations on data, depending on the specific requirements of the task at hand.