Functions - SQL Server

Database Functions are a set of SQL commands used to perform specific database transactions.

SQL Server provides both built-in functions and user-defined functions.

Built-in functions

SQL server built-in functions are categorized as follows.

String functions

USE AdventureWorks2019;
GO
--Get the ASCII value of a character/string
SELECT ASCII('a');
SELECT ASCII('sentence');
--Convert to character
SELECT CHAR(97);
--Get the LENGTH of a given string/column and CONCATENATE
SELECT EMP_NAME, LEN(EMP_NAME) AS LENGTH, CONCAT('Length is = ',LEN(EMP_NAME))
FROM EMPLOYEE;
--result set
--MYSQL 5 Length is = 5
--ORACLE 6 Length is = 6
--Print 'n' characters from the left
SELECT LEFT('Database',3);
--result set: Dat
--Print 'n' number of characters from the right.
SELECT RIGHT('EXPRESSION',4);
--result set: SION
--Covert the string to lowercase
SELECT LOWER('MSSQL');
--result set: mssql
--Covert the column value to uppercase
SELECT Name, UPPER(Name) FROM HumanResources.Department;
--Trim the space characters from the left-hand side.
SELECT LTRIM(' EXPRESSION ');
--Trim the space characters from the right-hand side.
SELECT RTRIM(' EXPRESSION ');
--Search and replace
SELECT REPLACE('RELATIONAL DATABASE', 'DATABASE', 'DB' );
--result set: RELATIONAL DB
--Returns a substring from a given string starting from the first integer up to the next.
SELECT SUBSTRING('RELATIONAL DATABASE',8,3);

Numeric/Math functions

  1. ABS
--Select absolute value
SELECT ABS(-2332.11);

result set: 2332.11

  1. AVG, SUM, MIN, MAX
--Compute average, sum, minimum and maximum value out of the selected column.
SELECT AVG(StandardCost) AS AVG, SUM(StandardCost) AS SUM, MIN(StandardCost) AS MIN, MAX(StandardCost) AS MAX
FROM Production.ProductCostHistory;

result set: 434.2658 171535.0024 0.8565 2171.2942

  1. FLOOR, CEILING
-- Determine equal to or lesser/greater than nearest integer value for a given number
SELECT 34.79 AS 'NUMBER', FLOOR(34.79) AS 'FLOOR', CEILING(34.79) AS 'CEILING';

result set: 34.79 34 35

  1. ROUND
--Round a number to the second argument
SELECT ROUND(1.298,2);

result set: 1.300

  1. SIGN
--Determine the sign of a number, greater than 0 returns 1, less than 0 returns -1, equal to 0 returns 0
SELECT SIGN(3), SIGN(-38), SIGN(0);

result set: 1 -1 0

Date/Time functions

  1. GETDATE, DATEADD
--Increment/decrement year/month/date by a value
SELECT GETDATE() AS 'CURRENT_DATE',
DATEADD(year, 1, GETDATE()) AS 'ADD_YEAR',
DATEADD(month,4,GETDATE()) AS 'ADD_MONTH',
DATEADD(day, -10, GETDATE()) AS 'DECREASE_DAYS';

result set: 2021-10-17 22:42:15.290 2022-10-17 22:42:15.290 2022-02-17 22:42:15.290 2021-10-07 22:42:15.290

  1. DATEDIFF
--Calculate the year/month/day differences between 2 dates
SELECT DATEDIFF(YEAR, '2019/10/28 06:00', '2021/10/18 07:00'),
DATEDIFF(MONTH, '2021/05/28', '2021/10/18'),
DATEDIFF(day, '2021/10/8', '2021/10/18'),
DATEDIFF(HOUR, '2021/10/8 06:00', '2021/10/8 08:00'),
DATEDIFF(MINUTE, '2021/10/8 06:40', '2021/10/8 07:00');

result set: 2 5 10 2 20

  1. DATENAME
--Get the year/month/day/hour/minute/seconds from the current date (or any date)
SELECT GETDATE(),
DATENAME(YEAR, GETDATE()),
DATENAME(MONTH, GETDATE()),
DATENAME(DAY, GETDATE()),
DATENAME(HOUR, GETDATE()),
DATENAME(MINUTE, GETDATE()),
DATENAME(QUARTER, GETDATE());

result set: 2021-10-17 23:09:23.083 2021 October 17 23 9 4

  1. DATEPART
--Returns a specified part of a date
SELECT GETDATE(),
DATEPART(YEAR, GETDATE()),
DATEPART(MONTH, GETDATE()),
DATEPART(DAY, GETDATE()),
DATEPART(HOUR, GETDATE()),
DATEPART(MINUTE, GETDATE()),
DATEPART(QUARTER, GETDATE());

result set: 2021-10-17 23:14:25.297 2021 10 17 23 14 4

DATENAME and DATEPART functions are the same, except for their return date types.

Conversion functions

  1. CAST
--Cast one value to another data type
SELECT '16.28',
CAST (16.28 AS INT),
CAST (16.28 AS FLOAT),
CAST (GETDATE() AS VARCHAR(4)),
CAST('2021-10-10' AS datetime);

result set: 16.28 16 16.28 Oct 2021-10-10 00:00:00.000

  1. CONVERT
--Convert value from one data type to another, returns an error if the conversion fails. Otherwise it returns the converted value.
SELECT CONVERT(INT, 39.79);

result set: 39

  1. TRY_CAST
--Try to cast from one datatype to another, and if the conversion fails returns `NULL`
SELECT TRY_CAST(81.18 AS VARCHAR(5)), TRY_CAST(81.18 AS VARCHAR(2));

81.18 NULL

  1. TRY_CONVERT
--Try to convert from one datatype to another, and if the conversion fails returns `NULL`
SELECT TRY_CONVERT(INT, 'expression');

NULL

Advanced functions

USE HASURA;
GO
--Returns the name of the current user in the SQL Server database.
--Returns the login name information for the current user in the SQL Server database.
--Returns the user name of the current session in the SQL Server database.
--Validates if the value is a date(1) or not(0)
--Validates if the value is NULL, then return an alternate value
--Validates if value is numeric
SELECT CURRENT_USER, SYSTEM_USER, SESSION_USER,
ISDATE(EMPNAME) AS ISDATE, EmpID, ISNULL(DEPTID,0), ISNUMERIC(EMPID)
FROM EMPLOYEE;
Did you find this page helpful?
Start with GraphQL on Hasura for Free
  • ArrowBuild apps and APIs 10x faster
  • ArrowBuilt-in authorization and caching
  • Arrow8x more performant than hand-rolled APIs
Promo
footer illustration
Brand logo
© 2024 Hasura Inc. All rights reserved
Github
Titter
Discord
Facebook
Instagram
Youtube
Linkedin