SQL Server Functions

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 expression String Required Integer Returns integer representation of the first character of the expression. CHAR expression Integer Required Character Returns character (ASCII) representation of the expression. The expression must be between 0 and 255. CHARINDEX expression to search for String Required expression to be searched String Required startIndex Integer Optional 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 expression(s) String Required String Return string with n number of expression(s) merged end to end, where n is 1 or greater. CONCAT_WS expression(s) String Required String Same as CONCAT except inserts a separator between expression(s). DIFFERENCE expression 1 String Required expression 2 String Required Integer Return integer difference betwen the SOUNDEX() values of expression 1 and expression 2. FORMAT value Any Required format String Required culture String Optional String Return string with value in format specified by format. LEFT expression String Required number Integer Required String Return string with the leftmost number of characters of expression. LEN string String Required Integer Return length of string, excluding trailing spaces. LOWER expression String Required String Return string with the expression converted to lowercase. LTRIM expression String Required String Return string with leading spaces removed from expression. NCHAR expression Integer Required Character Return unicode character of expression. PATINDEX pattern String Required expression String Required Integer Return integer location of first occurrence of pattern found in expression</strong. Returns 0 if pattern is not found. QUOTENAME expression String Required quote character Character Optional String Return Unicode string expression with delimiter quote character to make valid SQL Identifier. REPLACE expression String Required expression to be replaced String Required replacement expression String Required String Return string with expression to be replaced found in expression replaced by replacement expression. REPLICATE expression String Required number Integer Required String Return string made up expression repeated number times. REVERSE expression String Required String Return string from expression where characters are added from the back to the front order. RIGHT expression String Required number Integer Required String Return string with the rightmost number of characters of expression. RTRIM expression String Required String Return a string with trailing spaces removed from expression. SOUNDEX expression String Required String Return string based on how expression sounds. SPACE expression Integer Required String Return string made up of expression number of spaces. STR string Float Required length Integer Optional decimal Integer Optional 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] expression String Required separator String Required 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 expression String Required type String Required String Return string where expression has characters escaped according to type. Currently, type supports 'json'. STUFF expression String Required start index Integer Required length Integer Required replacement expression String Required String Return string where length long part of expression is removed and replaced with replacement expression. SUBSTRING expression String Required index Integer Required length Integer Required 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 expression to be manipulated String Required expression with characters to be replaced String Required expression with characters to be replaced with String Required 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 expression String Required String Return a string with expression, having leading and trailing spaces removed. UNICODE expression String Required Integer Return unicode of first character of expression. UPPER expression String Required String Return a string with expression converted to uppercase String Functions 3.2 SQL Server Math/Numeric Functions Name Parameters Return Type Description ABS expression Float Required Float Return absolute(positive) value of expression. ACOS expression Float Required Float Return angle, in radians whose cosine is equal to expression. expression must be between -1.0 and 1.0. ASIN expression Float Required Float Return angle, in radians whose sine is equal to expression. expression must be between -1.0 and 1.0 ATAN expression Float Required Float Return angle, in radians whose tangent is equal to expression. ATN2 y Float Required x Number Required Float Return angle, in radians whose tangent has a x-coordinate of x and a y-coordinate of y. CEILING expression Float Required Integer Return smallest integer number greater than or equal to expression. COS expression Float Required Float Return cosine of the expression(in radians) COT expression Float Required Float Return cotangent of the expression(in radians). DEGREES expression Float Required Float Return degrees of the expression(in radians). EXP expression Float Required Float Return e (~2.71) raised to expression power. FLOOR expression Float Required Integer Return greatest integer less than or equal to expression. LOG expression Float Required Float Return natural logarithm (base e) of expression. LOG10 expression Float Required Float Return logarithm (base 10) of expression. PI No arguments Float Return value of PI (~3.14). POWER expression Float Required exponent Float Required Float Return float of expression raised to the power of exponent. RADIANS expression Float Required Float Return radian equivalent of expression(in degrees) RAND seed Integer Optional Float Return randomly generated number between 0 and 1, exclusive. ROUND expression Number Required length Integer Required function Number Optional Number Return rounded off expression as specified by length. SIGN expression Float Required Number Return -1 if expression is negative, +1 if expression is positive, otherwise 0. SIN expression Float Required Float Return sine of expression (in radians). SQRT expression Float Required Float Return square root of expression. SQUARE expression Float Required Float Return square of expression or expression times expression. TAN expression Float Required Float Return tangent of expression (in radians). Math/Numeric Functions 3.3 SQL Server Date Functions Name Parameters Return Type Description CURRENT_TIMESTAMP No arguments DateTime Return current datetime DATEADD datepart (see below) Required Date Part Equivalents year yy, yyyy quarter q, qq month m, mm dayofyear dy, y day d, dd week wk,ww weekday dw,w hour hh minute mi, n second s, ss millisecond ms microsecond mcs nanosecond ns expression Integer Required date Date,DateTime, DateTimeOffset, DateTime2, SmallDateTime or Time Required DateTime Return datetime with expression added to date. What is added is determined by datepart DATEDIFF datepart (see below) Required Date Part Equivalents year yy, yyyy quarter q, qq month m, mm dayofyear dy, y day d, dd week wk,ww weekday dw,w hour hh minute mi, n second s, ss millisecond ms microsecond mcs nanosecond ns date 1 DateTime Required date 2 DateTime Required Integer Return integer difference between date 1 and date 2. datepart determines what time units to get difference of. DATEDIFF_BIG datepart (see below) Required Date Part Equivalents year yy, yyyy quarter q, qq month m, mm dayofyear dy, y day d, dd week wk,ww weekday dw,w hour hh minute mi, n second s, ss millisecond ms microsecond mcs nanosecond ns date 1 DateTime Required date 2 DateTime Required Big Integer Allows for larger numbers than DATEDIFF DATEFROMPARTS year Number Required month Number Required day Number Required DateTime Return dateTime with year-month-day DATENAME datepart (see below) Required Date Part Equivalents year yy, yyyy quarter q, qq month m, mm dayofyear dy, y day d, dd week wk,ww weekday dw,w hour hh minute mi, n second s, ss millisecond ms microsecond mcs nanosecond ns date DateTime Required String Return string representation part of date as determined by datepart. DATEPART datepart (see below) Required Date Part Equivalents year yy, yyyy quarter q, qq month m, mm dayofyear dy, y day d, dd week wk,ww weekday dw,w hour hh minute mi, n second s, ss millisecond ms microsecond mcs nanosecond ns date DateTime Required Integer Return integer part of date as determined by datepart. DATETIME2FROMPARTS year Integer Required month Integer Required day Integer Required hour Integer Required minute Integer Required seconds Integer Required fractions Integer Required precision Integer Required DateTime Return dateTime with year-month-day hour:minute:seconds.fractions with precision. DATETIMEFROMPARTS year Integer Required month Integer Required day Integer Required hour Integer Required minute Integer Required seconds Integer Required milliseconds Integer Required DateTime Return dateTime with year-month-day hour:minute:seconds.milliseconds. DATETIMEOFFSETFROMPARTS year Integer Required month Integer Required day Integer Required hour Integer Required minute Integer Required seconds Integer Required fractions Integer Required hour offset Integer Required minute offset Integer Required precision Integer Required DateTime Offset Return dateTime offset with year-month-day hour:minute:seconds.fractions with precision. hour offset and minute offset represent timezone offset. DAY date DateTime Required Integer Return day part of date. EOMONTH date DateTime Required DateTime Return datetime of the last day of the month specified by the date. GETDATE No arguments DateTime Return current datetime GETUTCDATE No arguments DateTime Return current datetime ISDATE date String Required Integer Return 1 if date is a valid date, 0 otherwise. MONTH date DateTime Required Integer Return month number of date. SMALLDATETIMEFROMPARTS year Integer Required month Integer Required day Integer Required hour Integer Required minute Integer Required Small DateTime Return dateTime with year-month-day hour:minute. SWITCHOFFSET dt offset expression datetime offset Required tz offset expression timezone offset expression Required DateTime Offset Return datetime offset in different time zone. SYSDATETIME No arguments DateTime Return current datetime SYSDATETIMEOFFSET No arguments DateTime Offset Return current datetime offset. SYSUTCDATETIME No arguments DateTime Return current datetime TIMEFROMPARTS hour Integer Required minute Integer Required seconds Integer Required fractions Integer Required precision Integer Required Time Return time with hour:minute:seconds.fractions with precision. TODATETIMEOFFSET dt expression DateTime Required tz offset expression timezone offset expression Required DateTime Offset Return datetime offset in different time zone of dt expression depending on tz offset expression. YEAR date time, date, smalldatetime, datetime, datetime2 or datetimeoffset Required Integer Return year of date. Date Functions 3.4 SQL Server Advanced Functions A subset of additional functions will be included in the following table: Name Parameters Return Type Description AVG expression(s) Number Required Number Return average of values (from rows of numeric fields) in group matching a criteria. COUNT expression(s) Any Required Integer Returns count of values (rows) matching a criteria. MAX expression(s) Any Required Any Returns maximum value for group of values (rows of fields) matching a criteria. MIN expression(s) Any Required Any Returns minimum value for group of values (rows of fields) matching a criteria. SUM expression(s) Number Required Number Return sum of values (from rows of numeric fields) in group matching a criteria. ISNULL check expression Any Required replacement value Any Required Any Return expression is not null, otherise replacement value. ISNUMERIC expression Any Required Integer Return 1 if expression is numeric, otherwise 0. HOST_NAME No Arguments String Return name of workstation/server. HOST_ID No Arguments String Return identification number of workstation/server. Advanced Functions 4. How are functions used? SQL Functions are used in queriesSQL 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.

SQL Server Functions

Thank you!

Frank Yee

Thank you!

Related Articles