{"id":165,"date":"2018-12-06T10:19:33","date_gmt":"2018-12-06T10:19:33","guid":{"rendered":"https:\/\/barkhane.com\/sql\/2018\/12\/06\/sql-server-functions\/"},"modified":"2024-03-03T18:01:13","modified_gmt":"2024-03-03T18:01:13","slug":"sql-server-functions","status":"publish","type":"post","link":"https:\/\/barkhane.com\/sql\/sql-server-functions\/","title":{"rendered":"SQL Server Functions"},"content":{"rendered":"

SQL Server has many built-in functions. SQL Server Functions<\/p>\n

This reference contains string, numeric, date, conversion, and some advanced functions in SQL Server.<\/p>\n

SQL Server String Functions<\/h2>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
Function<\/strong><\/th>\nDescription<\/th>\n<\/tr>\n
ASCII<\/strong><\/td>\nReturns the ASCII value for the specific character<\/td>\n<\/tr>\n
CHAR<\/strong><\/td>\nReturns the character based on the ASCII code<\/td>\n<\/tr>\n
CHARINDEX<\/strong><\/td>\nReturns the position of a substring in a string<\/td>\n<\/tr>\n
CONCAT<\/strong><\/td>\nAdds two or more strings together<\/td>\n<\/tr>\n
Concat with +<\/strong><\/td>\nAdds two or more strings together<\/td>\n<\/tr>\n
CONCAT_WS<\/strong><\/td>\nAdds two or more strings together with a separator<\/td>\n<\/tr>\n
DATALENGTH<\/strong><\/td>\nReturns the number of bytes used to represent an expression<\/td>\n<\/tr>\n
DIFFERENCE<\/strong><\/td>\nCompares two SOUNDEX values, and returns an integer value<\/td>\n<\/tr>\n
FORMAT<\/strong><\/td>\nFormats a value with the specified format<\/td>\n<\/tr>\n
LEFT<\/strong><\/td>\nExtracts a number of characters from a string (starting from left)<\/td>\n<\/tr>\n
LEN<\/strong><\/td>\nReturns the length of a string<\/td>\n<\/tr>\n
LOWER<\/strong><\/td>\nConverts a string to lower-case<\/td>\n<\/tr>\n
LTRIM<\/strong><\/td>\nRemoves leading spaces from a string<\/td>\n<\/tr>\n
NCHAR<\/strong><\/td>\nReturns the Unicode character based on the number code<\/td>\n<\/tr>\n
PATINDEX<\/strong><\/td>\nReturns the position of a pattern in a string<\/td>\n<\/tr>\n
QUOTENAME<\/strong><\/td>\nReturns a Unicode string with delimiters added to make the string a valid SQL Server delimited identifier<\/td>\n<\/tr>\n
REPLACE<\/strong><\/td>\nReplaces all occurrences of a substring within a string, with a new substring<\/td>\n<\/tr>\n
REPLICATE<\/strong><\/td>\nRepeats a string a specified number of times<\/td>\n<\/tr>\n
REVERSE<\/strong><\/td>\nReverses a string and returns the result<\/td>\n<\/tr>\n
RIGHT<\/strong><\/td>\nExtracts a number of characters from a string (starting from right)<\/td>\n<\/tr>\n
RTRIM<\/strong><\/td>\nRemoves trailing spaces from a string<\/td>\n<\/tr>\n
SOUNDEX<\/strong><\/td>\nReturns a four-character code to evaluate the similarity of two strings<\/td>\n<\/tr>\n
SPACE<\/strong><\/td>\nReturns a string of the specified number of space characters<\/td>\n<\/tr>\n
STR<\/strong><\/td>\nReturns a number as string<\/td>\n<\/tr>\n
STUFF<\/strong><\/td>\nDeletes a part of a string and then inserts another part into the string, starting at a specified position<\/td>\n<\/tr>\n
SUBSTRING<\/strong><\/td>\nExtracts some characters from a string<\/td>\n<\/tr>\n
TRANSLATE<\/strong><\/td>\nReturns the string from the first argument after the characters specified in the second argument are translated into the characters specified in the third argument.<\/td>\n<\/tr>\n
TRIM<\/strong><\/td>\nRemoves leading and trailing spaces (or other specified characters) from a string<\/td>\n<\/tr>\n
UNICODE<\/strong><\/td>\nReturns the Unicode value for the first character of the input expression<\/td>\n<\/tr>\n
UPPER<\/strong><\/td>\nConverts a string to upper-case<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
\n
\n
<\/div>\n<\/div>\n
\n

SQL Server Math\/Numeric Functions<\/h2>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
Function<\/strong><\/th>\nDescription<\/th>\n<\/tr>\n
ABS<\/strong><\/td>\nReturns the absolute value of a number<\/td>\n<\/tr>\n
ACOS<\/strong><\/td>\nReturns the arc cosine of a number<\/td>\n<\/tr>\n
ASIN<\/strong><\/td>\nReturns the arc sine of a number<\/td>\n<\/tr>\n
ATAN<\/strong><\/td>\nReturns the arc tangent of a number<\/td>\n<\/tr>\n
ATN2<\/strong><\/td>\nReturns the arc tangent of two numbers<\/td>\n<\/tr>\n
AVG<\/strong><\/td>\nReturns the average value of an expression<\/td>\n<\/tr>\n
CEILING<\/strong><\/td>\nReturns the smallest integer value that is >= a number<\/td>\n<\/tr>\n
COUNT<\/strong><\/td>\nReturns the number of records returned by a select query<\/td>\n<\/tr>\n
COS<\/strong><\/td>\nReturns the cosine of a number<\/td>\n<\/tr>\n
COT<\/strong><\/td>\nReturns the cotangent of a number<\/td>\n<\/tr>\n
DEGREES<\/strong><\/td>\nConverts a value in radians to degrees<\/td>\n<\/tr>\n
EXP<\/strong><\/td>\nReturns e raised to the power of a specified number<\/td>\n<\/tr>\n
FLOOR<\/strong><\/td>\nReturns the largest integer value that is <= to a number<\/td>\n<\/tr>\n
LOG<\/strong><\/td>\nReturns the natural logarithm of a number, or the logarithm of a number to a specified base<\/td>\n<\/tr>\n
LOG10<\/strong><\/td>\nReturns the natural logarithm of a number to base 10<\/td>\n<\/tr>\n
MAX<\/strong><\/td>\nReturns the maximum value in a set of values<\/td>\n<\/tr>\n
MIN<\/strong><\/td>\nReturns the minimum value in a set of values<\/td>\n<\/tr>\n
PI<\/strong><\/td>\nReturns the value of PI<\/td>\n<\/tr>\n
POWER<\/strong><\/td>\nReturns the value of a number raised to the power of another number<\/td>\n<\/tr>\n
RADIANS<\/strong><\/td>\nConverts a degree value into radians<\/td>\n<\/tr>\n
RAND<\/strong><\/td>\nReturns a random number<\/td>\n<\/tr>\n
ROUND<\/strong><\/td>\nRounds a number to a specified number of decimal places<\/td>\n<\/tr>\n
SIGN<\/strong><\/td>\nReturns the sign of a number<\/td>\n<\/tr>\n
SIN<\/strong><\/td>\nReturns the sine of a number<\/td>\n<\/tr>\n
SQRT<\/strong><\/td>\nReturns the square of a number<\/td>\n<\/tr>\n
SQUARE<\/strong><\/td>\nReturns the square of a number<\/td>\n<\/tr>\n
SUM<\/strong><\/td>\nCalculates the sum of a set of values<\/td>\n<\/tr>\n
TAN<\/strong><\/td>\nReturns the tangent of a number<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
\n

SQL Server Date Functions<\/h2>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
Function<\/strong><\/th>\nDescription<\/th>\n<\/tr>\n
CURRENT_TIMESTAMP<\/strong><\/td>\nReturns the current date and time<\/td>\n<\/tr>\n
DATEADD<\/strong><\/td>\nAdds a time\/date interval to a date and then returns the date<\/td>\n<\/tr>\n
DATEDIFF<\/strong><\/td>\nReturns the difference between two dates<\/td>\n<\/tr>\n
DATEFROMPARTS<\/strong><\/td>\nReturns a date from the specified parts (year, month, and day values)<\/td>\n<\/tr>\n
DATENAME<\/strong><\/td>\nReturns a specified part of a date (as string)<\/td>\n<\/tr>\n
DATEPART<\/strong><\/td>\nReturns a specified part of a date (as integer)<\/td>\n<\/tr>\n
DAY<\/strong><\/td>\nReturns the day of the month for a specified date<\/td>\n<\/tr>\n
GETDATE<\/strong><\/td>\nReturns the current database system date and time<\/td>\n<\/tr>\n
GETUTCDATE<\/strong><\/td>\nReturns the current database system UTC date and time<\/td>\n<\/tr>\n
ISDATE<\/strong><\/td>\nChecks an expression and returns 1 if it is a valid date, otherwise 0<\/td>\n<\/tr>\n
MONTH<\/strong><\/td>\nReturns the month part for a specified date (a number from 1 to 12)<\/td>\n<\/tr>\n
SYSDATETIME<\/strong><\/td>\nReturns the date and time of the SQL Server<\/td>\n<\/tr>\n
YEAR<\/strong><\/td>\nReturns the year part for a specified date<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
\n

SQL Server Advanced Functions<\/h2>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
Function<\/strong><\/th>\nDescription<\/th>\n<\/tr>\n
CAST<\/strong><\/td>\nConverts a value (of any type) into a specified datatype<\/td>\n<\/tr>\n
COALESCE<\/strong><\/td>\nReturns the first non-null value in a list<\/td>\n<\/tr>\n
CONVERT<\/strong><\/td>\nConverts a value (of any type) into a specified datatype<\/td>\n<\/tr>\n
CURRENT_USER<\/strong><\/td>\nReturns the name of the current user in the SQL Server database<\/td>\n<\/tr>\n
ISNULL<\/strong><\/td>\nReturn a specified value if the expression is NULL, otherwise return the expression<\/td>\n<\/tr>\n
ISNUMERIC<\/strong><\/td>\nTests whether an expression is numeric<\/td>\n<\/tr>\n
NULLIF<\/strong><\/td>\nReturns NULL if two expressions are equal<\/td>\n<\/tr>\n
SESSION_USER<\/strong><\/td>\nReturns the name of the current user in the SQL Server database<\/td>\n<\/tr>\n
SESSIONPROPERTY<\/strong><\/td>\nReturns the session settings for a specified option<\/td>\n<\/tr>\n
SYSTEM_USER<\/strong><\/td>\nReturns the login name for the current user<\/td>\n<\/tr>\n
USER_NAME<\/strong><\/td>\nReturns the database user name based on the specified id<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n","protected":false},"excerpt":{"rendered":"

SQL Server has many built-in functions. SQL Server Functions This reference contains string, numeric, date, conversion, and some advanced functions […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"site-sidebar-layout":"default","site-content-layout":"","ast-site-content-layout":"default","site-content-style":"default","site-sidebar-style":"default","ast-global-header-display":"","ast-banner-title-visibility":"","ast-main-header-display":"","ast-hfb-above-header-display":"","ast-hfb-below-header-display":"","ast-hfb-mobile-header-display":"","site-post-title":"","ast-breadcrumbs-content":"","ast-featured-img":"","footer-sml-layout":"","ast-disable-related-posts":"","theme-transparent-header-meta":"","adv-header-id-meta":"","stick-header-meta":"","header-above-stick-meta":"","header-main-stick-meta":"","header-below-stick-meta":"","astra-migrate-meta-layouts":"default","ast-page-background-enabled":"default","ast-page-background-meta":{"desktop":{"background-color":"var(--ast-global-color-4)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"ast-content-background-meta":{"desktop":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"footnotes":""},"categories":[2],"tags":[],"class_list":["post-165","post","type-post","status-publish","format-standard","hentry","category-sql"],"_links":{"self":[{"href":"https:\/\/barkhane.com\/sql\/wp-json\/wp\/v2\/posts\/165","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/barkhane.com\/sql\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/barkhane.com\/sql\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/barkhane.com\/sql\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/barkhane.com\/sql\/wp-json\/wp\/v2\/comments?post=165"}],"version-history":[{"count":0,"href":"https:\/\/barkhane.com\/sql\/wp-json\/wp\/v2\/posts\/165\/revisions"}],"wp:attachment":[{"href":"https:\/\/barkhane.com\/sql\/wp-json\/wp\/v2\/media?parent=165"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/barkhane.com\/sql\/wp-json\/wp\/v2\/categories?post=165"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/barkhane.com\/sql\/wp-json\/wp\/v2\/tags?post=165"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}