SQL Server Scalar Functions (Non-aggregate)
SQL Server has many built-in functions.
This reference contains string, numeric, date, conversion, and some advanced functions in SQL Server.
SQL Server String Functions
Function | Description | Syntax |
CHAR | Returns the character based on the ASCII code | SELECT CHAR(65) AS CodeToCharacter; |
CHARINDEX | Returns the position of a substring in a string | SELECT CHARINDEX('t', 'Customer') AS MatchPosition; |
CONCAT | Adds two or more strings together | SELECT CONCAT('Gyansetu', '.com'); |
Concat with + | Adds two or more strings together | SELECT 'Gyansetu' + '.com'; |
CONCAT_WS | Adds two or more strings together with a separator | SELECT CONCAT_WS('.', 'www', 'Gyansetu', 'com'); |
LEFT | Extracts a few characters from a string (starting from left) | SELECT LEFT('SQL Tutorial', 3) AS ExtractString; |
LEN | Returns the length of a string | SELECT LEN('Gyansetu.com'); |
LOWER | Converts a string to lower-case | SELECT LOWER('SQL Tutorial is FUN!'); |
LTRIM | Removes leading spaces from a string | SELECT LTRIM(' SQL Tutorial') AS LeftTrimmedString; |
REPLACE | Replaces all occurrences of a substring within a string, with a new string | SELECT REPLACE('SQL Tutorial', 'T', 'M'); |
RIGHT | Extracts a few characters from a string (starting from right) | SELECT RIGHT('SQL Tutorial', 3) AS ExtractString; |
RTRIM | Removes trailing spaces from a string | SELECT RTRIM('SQL Tutorial ') AS RightTrimmedString; |
STR | Returns a number as string | SELECT STR(185); |
SUBSTRING | Extracts some characters from a string | SELECT SUBSTRING('SQL Tutorial', 1, 3) AS ExtractString; |
TRIM | Removes leading and trailing spaces (or other specified characters) from a string | SELECT TRIM(' SQL Tutorial! ') AS TrimmedString; |
UPPER | Converts a string to upper-case | SELECT UPPER('SQL Tutorial is FUN!'); |
SQL Server Math/Numeric Functions
Function | Description | Syntax |
ABS | Returns the absolute value of a number | SELECT Abs(-243.5) AS AbsNum; |
AVG | Returns the average value of an expression | SELECT AVG(Price) AS AveragePrice FROM Products; |
CEILING | Returns the smallest integer value that is >= a number | SELECT CEILING(25.75) AS CeilValue; |
COUNT | Returns the number of records returned by a select query | SELECT COUNT(ProductID) AS NumberOfProducts FROM Products; |
FLOOR | Returns the largest integer value that is <= to a number | SELECT FLOOR(25.75) AS FloorValue; |
MAX | Returns the maximum value in a set of values | SELECT MAX(Price) AS LargestPrice FROM Products; |
MIN | Returns the minimum value in a set of values | SELECT MIN(Price) AS SmallestPrice FROM Products; |
POWER | Returns the value of a number raised to the power of another number | SELECT POWER(4, 2); |
ROUND | Rounds a number to a specified number of decimal places | SELECT ROUND(235.415, 2) AS RoundValue; |
SQRT | Returns the square root of a number | SELECT SQRT(64); |
SQUARE | Returns the square of a number | SELECT SQUARE(64); |
SUM | Calculates the sum of a set of values | SELECT SUM(quantity) as TotalItemsOrdered FROM OrderDetails |
SQL Server Date Functions
Function | Description | Syntax |
CURRENT_TIMESTAMP | Returns the current date and time | SELECT CURRENT_TIMESTAMP; |
DATEADD | Adds a time/date interval to a date and then returns the date | SELECT DATEADD(year, 1, '2017/08/25') AS DateAdd; |
DATEDIFF | Returns the difference between two dates | SELECT DATEDIFF(year, '2017/08/25', '2011/08/25') AS DateDiff; |
DATEPART | Returns a specified part of a date (as integer) | SELECT DATEPART(year, '2017/08/25') AS DatePartInt; |
DAY | Returns the day of the month for a specified date | SELECT DAY('2017/08/25') AS DayOfMonth; |
GETDATE | Returns the current database system date and time | SELECT GETDATE(); |
ISDATE | Checks an expression and returns 1 if it is a valid date, else 0 | SELECT ISDATE('2017-08-25'); |
MONTH | Returns the month part for a specified date (1 - 12) | SELECT MONTH('2017/08/25') AS Month; |
SYSDATETIME | Returns the date and time of the SQL Server | SELECT SYSDATETIME() AS SysDateTime; |
YEAR | Returns the year part for a specified date | SELECT YEAR('2017/08/25') AS Year; |
SQL Server Advanced Functions
Function | Description | Syntax |
CAST | Converts a value (of any type) into a specified datatype | CAST(expression AS datatype(length)) SELECT CAST(25.65 AS varchar); SELECT CAST('2017-08-25' AS datetime); |
CONVERT | Converts a value (of any type) into a specified datatype | CONVERT(data_type(length), expression, style) SELECT CONVERT(int, 25.65); SELECT CONVERT(varchar, '2017-08-25', 101); |
ISNULL | Return a specified value if the expression is NULL, otherwise return the expression | SELECT ISNULL(NULL, 'Gyansetu.com'); |
Last updated