1. Introduction
SQL Server is a relational database management system (RDBMS) solution created by Microsoft Corporation. Focus in this treatise will be on SQL Server functions, href=”https://docs.microsoft.com/en-us/sql/t-sql/functions/functions?view=sql-server-ver15. Some functions discussed here can also be found in other RDBMS.
2. Background
SQL functions, in general, typically will allow for parameters to be used to call them. This will result in a value that can be displayed or subsequently used as input to yet another function. This is known as the nesting of functions. Functions will activate instructions that can perform the calculation or manipulate the parameters, which can be any expression. An expression can be a value or a name of a column of a specified table.
3.1 SQL Server String Functions
| Name | Parameters | Return Type | Description | ||||||||||||
| ASCII |
| Integer | Returns integer representation of the first character of the expression. | ||||||||||||
| CHAR |
| Character | Returns character (ASCII) representation of the expression. The expression must be between 0 and 255. | ||||||||||||
| CHARINDEX |
| Integer | Return location of the first occurrence of the first character in expression to search for to be found in expression to be searched. The return value will be between 1 and the length of expression to be searched, or 0 if the character is not found. | ||||||||||||
| CONCAT |
| String | Return string with n number of expression(s) merged end to end, where n is 1 or greater. | ||||||||||||
| CONCAT_WS |
| String | Same as CONCAT except inserts a separator between expression(s). | ||||||||||||
| DIFFERENCE |
| Integer | Return integer difference betwen the SOUNDEX() values of expression 1 and expression 2. | ||||||||||||
| FORMAT |
| String | Return string with value in format specified by format. | ||||||||||||
| LEFT |
| String | Return string with the leftmost number of characters of expression. | ||||||||||||
| LEN |
| Integer | Return length of string, excluding trailing spaces. | ||||||||||||
| LOWER |
| String | Return string with the expression converted to lowercase. | ||||||||||||
| LTRIM |
| String | Return string with leading spaces removed from expression. | ||||||||||||
| NCHAR |
| Character | Return unicode character of expression. | ||||||||||||
| PATINDEX |
| Integer | Return integer location of first occurrence of pattern found in expression</strong. Returns 0 if pattern is not found. | ||||||||||||
| QUOTENAME |
| String | Return Unicode string expression with delimiter quote character to make valid SQL Identifier. | ||||||||||||
| REPLACE |
| String | Return string with expression to be replaced found in expression replaced by replacement expression. | ||||||||||||
| REPLICATE |
| String | Return string made up expression repeated number times. | ||||||||||||
| REVERSE |
| String | Return string from expression where characters are added from the back to the front order. | ||||||||||||
| RIGHT |
| String | Return string with the rightmost number of characters of expression. | ||||||||||||
| RTRIM |
| String | Return a string with trailing spaces removed from expression. | ||||||||||||
| SOUNDEX |
| String | Return string based on how expression sounds. | ||||||||||||
| SPACE |
| String | Return string made up of expression number of spaces. | ||||||||||||
| STR |
| String | Return string with expression expressed as string. Result is right-justified dependent on length(default: 10) and decimal(0-16). | ||||||||||||
| STRING_AGG [WHERE clause permitted] |
| String | Return string with expression end-to-end with separator inserted except for last occurrence. Where clause is permitted to allow for criteria to be specified for expression to be included. | ||||||||||||
| STRING_ESCAPE |
| String | Return string where expression has characters escaped according to type. Currently, type supports ‘json’. | ||||||||||||
| STUFF |
| String | Return string where length long part of expression is removed and replaced with replacement expression. | ||||||||||||
| SUBSTRING |
| String | Return string of part of expression starting at location index for length characters. Index of 1 indicates the start from first character of the string. length cannot exceed the length of expression. | ||||||||||||
| TRANSLATE |
| String | Return string with occurrences of expression with characters to be replaced found in expression to be manipulated replaced by expression with characters to be replaced with. | ||||||||||||
| TRIM |
| String | Return a string with expression, having leading and trailing spaces removed. | ||||||||||||
| UNICODE |
| Integer | Return unicode of first character of expression. | ||||||||||||
| UPPER |
| String | Return a string with expression converted to uppercase |
3.2 SQL Server Math/Numeric Functions
| Name | Parameters | Return Type | Description | |||||||||
| ABS |
| Float | Return absolute(positive) value of expression. | |||||||||
| ACOS |
| Float | Return angle, in radians whose cosine is equal to expression. expression must be between -1.0 and 1.0. | |||||||||
| ASIN |
| Float | Return angle, in radians whose sine is equal to expression. expression must be between -1.0 and 1.0 | |||||||||
| ATAN |
| Float | Return angle, in radians whose tangent is equal to expression. | |||||||||
| ATN2 |
| Float | Return angle, in radians whose tangent has a x-coordinate of x and a y-coordinate of y. | |||||||||
| CEILING |
| Integer | Return smallest integer number greater than or equal to expression. | |||||||||
| COS |
| Float | Return cosine of the expression(in radians) | |||||||||
| COT |
| Float | Return cotangent of the expression(in radians). | |||||||||
| DEGREES |
| Float | Return degrees of the expression(in radians). | |||||||||
| EXP |
| Float | Return e (~2.71) raised to expression power. | |||||||||
| FLOOR |
| Integer | Return greatest integer less than or equal to expression. | |||||||||
| LOG |
| Float | Return natural logarithm (base e) of expression. | |||||||||
| LOG10 |
| Float | Return logarithm (base 10) of expression. | |||||||||
| PI |
| Float | Return value of PI (~3.14). | |||||||||
| POWER |
| Float | Return float of expression raised to the power of exponent. | |||||||||
| RADIANS |
| Float | Return radian equivalent of expression(in degrees) | |||||||||
| RAND |
| Float | Return randomly generated number between 0 and 1, exclusive. | |||||||||
| ROUND |
| Number | Return rounded off expression as specified by length. | |||||||||
| SIGN |
| Number | Return -1 if expression is negative, +1 if expression is positive, otherwise 0. | |||||||||
| SIN |
| Float | Return sine of expression (in radians). | |||||||||
| SQRT |
| Float | Return square root of expression. | |||||||||
| SQUARE |
| Float | Return square of expression or expression times expression. | |||||||||
| TAN |
| Float | Return tangent of expression (in radians). |
3.3 SQL Server Date Functions
| Name | Parameters | Return Type | Description | ||||||||||||||||||||||||||||||||||||||||
| CURRENT_TIMESTAMP |
| DateTime | Return current datetime | ||||||||||||||||||||||||||||||||||||||||
| DATEADD |
| DateTime | Return datetime with expression added to date. What is added is determined by datepart | ||||||||||||||||||||||||||||||||||||||||
| DATEDIFF |
| Integer | Return integer difference between date 1 and date 2. datepart determines what time units to get difference of. | ||||||||||||||||||||||||||||||||||||||||
| DATEDIFF_BIG |
| Big Integer | Allows for larger numbers than DATEDIFF | ||||||||||||||||||||||||||||||||||||||||
| DATEFROMPARTS |
| DateTime | Return dateTime with year-month-day | ||||||||||||||||||||||||||||||||||||||||
| DATENAME |
| String | Return string representation part of date as determined by datepart. | ||||||||||||||||||||||||||||||||||||||||
| DATEPART |
| Integer | Return integer part of date as determined by datepart. | ||||||||||||||||||||||||||||||||||||||||
| DATETIME2FROMPARTS |
| DateTime | Return dateTime with year-month-day hour:minute:seconds.fractions with precision. | ||||||||||||||||||||||||||||||||||||||||
| DATETIMEFROMPARTS |
| DateTime | Return dateTime with year-month-day hour:minute:seconds.milliseconds. | ||||||||||||||||||||||||||||||||||||||||
| DATETIMEOFFSETFROMPARTS |
| DateTime Offset | Return dateTime offset with year-month-day hour:minute:seconds.fractions with precision. hour offset and minute offset represent timezone offset. | ||||||||||||||||||||||||||||||||||||||||
| DAY |
| Integer | Return day part of date. | ||||||||||||||||||||||||||||||||||||||||
| EOMONTH |
| DateTime | Return datetime of the last day of the month specified by the date. | ||||||||||||||||||||||||||||||||||||||||
| GETDATE |
| DateTime | Return current datetime | ||||||||||||||||||||||||||||||||||||||||
| GETUTCDATE |
| DateTime | Return current datetime | ||||||||||||||||||||||||||||||||||||||||
| ISDATE |
| Integer | Return 1 if date is a valid date, 0 otherwise. | ||||||||||||||||||||||||||||||||||||||||
| MONTH |
| Integer | Return month number of date. | ||||||||||||||||||||||||||||||||||||||||
| SMALLDATETIMEFROMPARTS |
| Small DateTime | Return dateTime with year-month-day hour:minute. | ||||||||||||||||||||||||||||||||||||||||
| SWITCHOFFSET |
| DateTime Offset | Return datetime offset in different time zone. | ||||||||||||||||||||||||||||||||||||||||
| SYSDATETIME |
| DateTime | Return current datetime | ||||||||||||||||||||||||||||||||||||||||
| SYSDATETIMEOFFSET |
| DateTime Offset | Return current datetime offset. | ||||||||||||||||||||||||||||||||||||||||
| SYSUTCDATETIME |
| DateTime | Return current datetime | ||||||||||||||||||||||||||||||||||||||||
| TIMEFROMPARTS |
| Time | Return time with hour:minute:seconds.fractions with precision. | ||||||||||||||||||||||||||||||||||||||||
| TODATETIMEOFFSET |
| DateTime Offset | Return datetime offset in different time zone of dt expression depending on tz offset expression. | ||||||||||||||||||||||||||||||||||||||||
| YEAR |
| Integer | Return year of date. | ||||||||||||||||||||||||||||||||||||||||
3.4 SQL Server Advanced Functions
A subset of additional functions will be included in the following table:
| Name | Parameters | Return Type | Description | ||||||
| AVG |
| Number | Return average of values (from rows of numeric fields) in group matching a criteria. | ||||||
| COUNT |
| Integer | Returns count of values (rows) matching a criteria. | ||||||
| MAX |
| Any | Returns maximum value for group of values (rows of fields) matching a criteria. | ||||||
| MIN |
| Any | Returns minimum value for group of values (rows of fields) matching a criteria. | ||||||
| SUM |
| Number | Return sum of values (from rows of numeric fields) in group matching a criteria. | ||||||
| ISNULL |
| Any | Return expression is not null, otherise replacement value. | ||||||
| ISNUMERIC |
| Integer | Return 1 if expression is numeric, otherwise 0. | ||||||
| HOST_NAME |
| String | Return name of workstation/server. | ||||||
| HOST_ID |
| String | Return identification number of workstation/server. |
4. How are functions used?
- SQL Functions are used in queries
- SQL Functions are used in database applications ( stored procedures or stored functions ). Stored procedures/functions are programs that are created and associated with a RDBMS. They are typically executed on the database server and are associated with a specific database. They are also typically migrated onto other servers/environments along with the database.
5. Summary
Software development encourages reuse and modularization. “Why invent the Wheel ?”. Tools, such as functions, have been developed, used often, tweaked, and re-tweaked. They are used to build bigger and more sophisticated tools (applications) to solve a particular problem. Functions are in the toolbox of software developers to build applications.
