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