SQL Functions
A function is a database object in SQL Server. Basically, it is a set of SQL statements that accept only input parameters, perform actions and return the result. The function can return only a single value or a table. We can't use a function to Insert, Update, Delete records in the database table(s)
They are two types mainly.
1. System Defined Functions
2. User Defined Functions
System Defined Functions
User Defined Functions
System Defined Functions
SQL Provides many useful built in functions and new ones are added time to time. We need to make sure we are using the updated version on SSMS in order to use them.
SQL STRING FUNCTIONS
| Function Name | Decsription |
|---|---|
| UPPER | Converts a string to upper-case |
| UNICODE | Returns the Unicode value for the first character of the input expression |
| TRIM | Removes leading and trailing spaces (or other specified characters) from a string |
| TRANSLATE | Returns the string from the first argument after the characters specified in the second argument are translated into the characters specified in the third argument. |
| SUBSTRING | Extracts some characters from a string |
| STUFF | Deletes a part of a string and then inserts another part into the string, starting at a specified position |
| STR | Returns a number as string |
| SPACE | Returns a string of the specified number of space characters |
| SOUNDEX | Returns a four-character code to evaluate the similarity of two strings |
| RTRIM | Removes trailing spaces from a string |
| RIGHT | Extracts a number of characters from a string (starting from right) |
| REVERSE | Reverses a string and returns the result |
| REPLICATE | Repeats a string a specified number of times |
| REPLACE | Replaces all occurrences of a substring within a string, with a new substring |
| QUOTENAME | Returns a Unicode string with delimiters added to make the string a valid SQL Server delimited identifier |
| CHAR | Returns the character based on the ASCII code |
| CHARINDEX | Returns the position of a substring in a string |
| CONCAT | Adds two or more strings together |
| Concat with + | Adds two or more strings together |
| CONCAT_WS | Adds two or more strings together with a separator |
| DATALENGTH | Returns the number of bytes used to represent an expression |
| DIFFERENCE | Compares two SOUNDEX values, and returns an integer value |
| FORMAT | Formats a value with the specified format |
| LEFT | Extracts a number of characters from a string (starting from left) |
| LEN | Returns the length of a string |
| LOWER | Converts a string to lower-case |
| LTRIM | Removes leading spaces from a string |
| NCHAR | Returns the Unicode character based on the number code |
| PATINDEX | Returns the position of a pattern in a string |
| ASCII | Returns the ASCII value for the specific character |
SQL NUMERIC FUNCTIONS
| Function Name | Decsription |
|---|---|
| AVG | Returns the average value of an expression |
| CEILING | Returns the smallest integer value that is >= a number |
| COUNT | Returns the number of records returned by a select query |
| COS | Returns the cosine of a number |
| COT | Returns the cotangent of a number |
| DEGREES | Converts a value in radians to degrees |
| EXP | Returns e raised to the power of a specified number |
| FLOOR | Returns the largest integer value that is <= to a number |
| LOG | Returns the natural logarithm of a number, or the logarithm of a number to a specified base | ABS | Returns the absolute value of a number |
| ACOS | Returns the arc cosine of a number |
| ASIN | Returns the arc sine of a number |
| ATAN | Returns the arc tangent of a number |
| ATN2 | Returns the arc tangent of two numbers |
| LOG10 | Returns the natural logarithm of a number to base 10 |
| MAX | Returns the maximum value in a set of values |
| MIN | Returns the minimum value in a set of values |
| PI | Returns the value of PI |
| POWER | Returns the value of a number raised to the power of another number |
| RADIANS | Converts a degree value into radians |
| RAND | Returns a random number |
| ROUND | Rounds a number to a specified number of decimal places |
| SIGN | Returns the sign of a number |
| SIN | Returns the sine of a number |
| SQRT | Returns the square root of a number |
| SQUARE | Returns the square of a number |
| SUM | Calculates the sum of a set of values |
| TAN | Returns the tangent of a number |
SQL DATE FUNCTIONS
| Function Name | Decsription |
|---|---|
| CURRENT_TIMESTAMP | Returns the current date and time |
| DATEADD | Adds a time/date interval to a date and then returns the date |
| DATEDIFF | Returns the difference between two dates |
| DATEFROMPARTS | Returns a date from the specified parts (year, month, and day values) |
| DATENAME | Returns a specified part of a date (as string) |
| DATEPART | Returns a specified part of a date (as integer) |
| DAY | Returns the day of the month for a specified date |
| GETDATE | Returns the current database system date and time |
| GETUTCDATE | Returns the current database system UTC date and time |
| ISDATE | Checks an expression and returns 1 if it is a valid date, otherwise 0 |
| MONTH | Returns the month part for a specified date (a number from 1 to 12) |
| SYSDATETIME | Returns the date and time of the SQL Server |
| YEAR | Returns the year part for a specified date |
User Defined Functions
Users can create there own functions which are refered as "user -defined functions". These functions can accept requried parameters , perform required actions and give the results. he user-defined functions hold the code that is needed to query data a lot easier to write. It also improves query readability, accessibility, and functionality, as well as allows other developers to replicate the same procedures accordingly.
These functions are created by the user in the system database or in a user-defined database. We have three types of user-defined functions.
1. Scalar Function
An SQL scalar function is a user-defined function written in SQL and it returns a single value each time it is invoked. SQL scalar functions contain the source code for the user-defined function in the user-defined function definition. There are two kinds of SQL scalar functions, inlined and compiled.
2. Inline Table-Valued Function
An inline table-valued function (iTVF) is a table expression that can accept parameters, perform an action and provide as its return value, a table.
3. Multi-Statement Table-Valued Function
What is multi statement table valued function in SQL Server? The multistatement table-valued, user-defined function combines the scalar function's capability to contain complex code with the inline table-valued function's capability to return a result set. This type of function creates a table variable and then populates it within code.