sqlsql

SQL Server Functions

Photo of Frank Yee Frank YeeApril 8th, 2022Last Updated: April 7th, 2022
0 64 8 minutes read

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 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.

Photo of Frank Yee Frank YeeApril 8th, 2022Last Updated: April 7th, 2022
0 64 8 minutes read
Photo of Frank Yee

Frank Yee

Frank holds a Bachelor of Science in Biology from The City University of New York (The City College of New York) and a Bachelor of Arts in Computer Science from The City University of New York (Hunter College). As an undergraduate, he had taken courses where programs were written in Fortran, Basic, Pascal, COBOL, and Assembly Language for the IBM 4341 Mainframe. He has been a software developer for over 30 years having built desktop apps, mobile apps and web apps. He has built software in C/C++, VisualBasic, TCL, Java, PHP, XML/XSLT, Perl, Shell Scripting and Python. Frank was also an educator in The City University of New York (Borough of Manhattan Community College, New York City College of Technology) having taught courses in Pascal, VisualBasic, C++ for over 10 years. Finally, he has recently helped students create programs in Lisp, R, Masm, Haskell, Scheme and Prolog.
Subscribe
Notify of
guest

This site uses Akismet to reduce spam. Learn how your comment data is processed.

0 Comments
Inline Feedbacks
View all comments
Back to top button