T-SQL Functions

What are T-SQL Functions?

T-SQL functions are pre-defined routines that perform a specific task, typically involving data. They accept input parameters, perform operations, and return a single value. Functions are crucial for manipulating data, performing calculations, and enhancing the readability and reusability of your SQL code.

Scalar Functions

Scalar functions return a single value for each row processed. These are the most common type of function.

GETDATE()

SELECT GETDATE() AS CurrentDateTime;

Aggregate Functions

Aggregate functions perform a calculation on a set of rows and return a single value. They are often used with the GROUP BY clause.

COUNT(expression)

SELECT COUNT(*) AS NumberOfOrders FROM Orders;

Table-Valued Functions (TVFs)

Table-valued functions return a table as their result set. They can be used in the FROM clause of a query.

CREATE FUNCTION dbo.GetEmployeeByDepartment (@DepartmentID INT)
RETURNS TABLE
AS
RETURN
(
    SELECT EmployeeID, FirstName, LastName
    FROM Employees
    WHERE DepartmentID = @DepartmentID
);

SELECT EmployeeID, FirstName, LastName
FROM dbo.GetEmployeeByDepartment(3);

String Functions

• LEN()
• LEFT()
• RIGHT()
• SUBSTRING()
• UPPER()
• LOWER()
• REPLACE()
• TRIM()

Numeric Functions

• ABS()
• ROUND()
• CEILING()
• FLOOR()
• POWER()

Date and Time Functions

• GETDATE()
• DATEPART()
• DATENAME()
• DATEDIFF()
• DATEADD()

System Functions

• USER_NAME()
• DB_NAME()
• HOST_NAME()

LEN()

LEN(string_expression)

SELECT LEN('SQL Functions') AS StringLength;
-- Result: 13

LEFT()

LEFT(character_expression, number_of_characters)

SELECT LEFT('Transact-SQL', 8) AS LeftPart;
-- Result: Transact

RIGHT()

RIGHT(character_expression, number_of_characters)

SELECT RIGHT('Transact-SQL', 3) AS RightPart;
-- Result: SQL

SUBSTRING()

SUBSTRING(expression, start, length)

SELECT SUBSTRING('SQL Server Functions', 5, 6) AS MiddlePart;
-- Result: Server

UPPER()

UPPER(character_expression)

SELECT UPPER('mixedCaseString') AS UppercaseString;
-- Result: MIXEDCASESTRING

LOWER()

LOWER(character_expression)

SELECT LOWER('MixedCaseString') AS LowercaseString;
-- Result: mixedcasestring

REPLACE()

REPLACE(string_expression, string_to_replace, replacement_string)

SELECT REPLACE('Hello World', 'World', 'SQL') AS ReplacedString;
-- Result: Hello SQL

TRIM()

TRIM( [ [ LEADING | TRAILING | BOTH ] [ characters FROM ] ] string_expression )

SELECT TRIM('   Spaces   ') AS TrimmedString;
-- Result: Spaces

ABS()

ABS(numeric_expression)

SELECT ABS(-100.5) AS AbsoluteValue;
-- Result: 100.5

ROUND()

ROUND(numeric_expression, length [, function])

SELECT ROUND(123.4567, 2) AS RoundedValue;
-- Result: 123.46

CEILING()

CEILING(numeric_expression)

SELECT CEILING(10.2) AS CeilingValue;
-- Result: 11

FLOOR()

FLOOR(numeric_expression)

SELECT FLOOR(10.8) AS FloorValue;
-- Result: 10

POWER()

POWER(numeric_expression, exponent)

SELECT POWER(2, 5) AS PowerResult;
-- Result: 32

GETDATE()

GETDATE()

SELECT GETDATE() AS CurrentDateTime;

DATEPART()

DATEPART(datepart, date)

SELECT DATEPART(year, '2023-10-27') AS YearPart;
-- Result: 2023

DATENAME()

DATENAME(datepart, date)

SELECT DATENAME(weekday, '2023-10-27') AS DayName;
-- Result: Friday

DATEDIFF()

DATEDIFF(datepart, startdate, enddate)

SELECT DATEDIFF(day, '2023-10-20', '2023-10-27') AS DaysDifference;
-- Result: 7

DATEADD()

DATEADD(datepart, number, date)

SELECT DATEADD(week, 1, '2023-10-27') AS DateAfterOneWeek;
-- Result: 2023-11-03

USER_NAME()

USER_NAME([user_id])

SELECT USER_NAME() AS CurrentUserName;

DB_NAME()

DB_NAME([database_id])

SELECT DB_NAME() AS CurrentDatabaseName;

HOST_NAME()

HOST_NAME()

SELECT HOST_NAME() AS ClientHostName;