Built-in Functions in SQL Server

Built-in Functions in SQL Server

Introduction

SQL Server provides a wide range of built-in functions that can be used to perform various operations on data stored in the database. These functions are categorized into different types based on their functionality, such as string manipulation, mathematical calculations, date and time operations, etc. This documentation will cover some of the most commonly used built-in functions in SQL Server along with code examples and explanations.

String Functions

1. LEN Function

The LEN function returns the length of a given string.

Syntax:

sql
LEN (string_expression)

Example:

sql
SELECT LEN('Hello, World!') AS Length;

Output:

markdown
Length
------
13

2. SUBSTRING Function

The SUBSTRING function extracts a portion of a string.

Syntax:

sql
SUBSTRING (string_expression, start, length)

Example:

sql
SELECT SUBSTRING('Hello, World!', 1, 5) AS ExtractedString;

Output:

markdown
ExtractedString
---------------
Hello

Mathematical Functions

1. SUM Function

The SUM function calculates the sum of values in a specified column.

Syntax:

sql
SUM (column_name)

Example:
Assuming we have a table named 'Sales' with a column 'Amount':

sql
SELECT SUM(Amount) AS TotalSales FROM Sales;

Output:

markdown
TotalSales
----------
25000.00

2. ABS Function

The ABS function returns the absolute value of a numeric expression.

Syntax:

sql
ABS (numeric_expression)

Example:

sql
SELECT ABS(-10) AS AbsoluteValue;

Output:

markdown
AbsoluteValue
-------------
10

Date and Time Functions

1. GETDATE Function

The GETDATE function retrieves the current system date and time.

Syntax:

sql
GETDATE()

Example:

sql
SELECT GETDATE() AS CurrentDateTime;

Output:

markdown
CurrentDateTime
---------------------
2023-07-23 12:34:56

2. DATEADD Function

The DATEADD function adds a specified time interval to a date.

Syntax:

sql
DATEADD (datepart, number, date)

Example:

sql
SELECT DATEADD(DAY, 7, '2023-07-23') AS NewDate;

Output:

markdown
NewDate
----------
2023-07-30

Conclusion

SQL Server offers a rich set of built-in functions that enhance the capabilities of SQL queries. This documentation covered some commonly used functions under the categories of string manipulation, mathematical calculations, and date and time operations. These examples provide a starting point for utilizing functions effectively in SQL Server queries, and there are many more functions available for different use cases and requirements.