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');

16KB

Useful SQL Server Functions.docx