SQL Server Functions - MSDN Tutorials

Understanding SQL Server Functions

Functions are crucial components in SQL Server that allow you to perform calculations, manipulate data, and control program flow. They can be broadly categorized into built-in functions provided by SQL Server and user-defined functions (UDFs) that you can create to encapsulate specific logic.

String Functions

These functions are used to manipulate string data types.

CONCAT

Concatenates two or more string values.

SELECT CONCAT('Microsoft ', 'SQL ', 'Server');

Microsoft SQL Server

LEN

Returns the number of characters in a string.

SELECT LEN('SQL Server Tutorials');

LOWER

Converts a string to lowercase.

SELECT LOWER('SQL Server');

sql server

UPPER

Converts a string to uppercase.

SELECT UPPER('SQL Server');

SQL SERVER

SUBSTRING

Extracts a part of a string starting from a specified position.

SELECT SUBSTRING('SQL Server', 5, 6);

Server

REPLACE

Replaces all occurrences of a specified substring with another substring.

SELECT REPLACE('Learn SQL Server', 'Learn', 'Master');

Master SQL Server

Numeric Functions

These functions operate on numeric data types.

ABS

Returns the absolute value of a number.

SELECT ABS(-123.45);

123.45

ROUND

Rounds a number to a specified number of decimal places.

SELECT ROUND(123.4567, 2);

123.46

CEILING

Returns the smallest integer greater than or equal to a number.

SELECT CEILING(4.2);

FLOOR

Returns the largest integer less than or equal to a number.

SELECT FLOOR(4.8);

RAND

Returns a random float value between 0 and 1.

SELECT RAND();

(A random number between 0 and 1)

Date & Time Functions

These functions are used for working with dates and times.

GETDATE

Returns the current database system date and time.

SELECT GETDATE();

(Current date and time)

DATEADD

Adds a specified time interval to a date.

SELECT DATEADD(day, 5, '2023-10-27');

2023-11-01

DATEDIFF

Returns the number of date/time parts between two specified dates.

SELECT DATEDIFF(day, '2023-10-20', '2023-10-27');

DATENAME

Returns a character string representing the specified datepart of the specified date.

SELECT DATENAME(weekday, '2023-10-27');

Friday

DATEPART

Returns an integer representing the specified datepart of the specified date.

SELECT DATEPART(month, '2023-10-27');

Aggregate Functions

Aggregate functions perform a calculation on a set of values and return a single value. Common examples include COUNT, SUM, AVG, MIN, and MAX.

                    -- Assuming a table named 'Products' with a 'Price' column
                    SELECT AVG(Price) AS AveragePrice
                    FROM Products
                    WHERE Category = 'Electronics';
                

User-Defined Functions (UDFs)

UDFs allow you to create your own functions to perform custom operations. They can improve code reusability and readability.

Scalar UDFs

Scalar UDFs return a single value. They are created using the CREATE FUNCTION statement.

                    CREATE FUNCTION dbo.CalculateDiscountedPrice (@OriginalPrice DECIMAL(10, 2), @DiscountRate DECIMAL(3, 2))
                    RETURNS DECIMAL(10, 2)
                    AS
                    BEGIN
                        RETURN @OriginalPrice * (1 - @DiscountRate);
                    END;
                

Usage:

SELECT dbo.CalculateDiscountedPrice(100.00, 0.15) AS FinalPrice;

85.00

Table-Valued UDFs

Table-valued UDFs return a table. They can be inline or multi-statement.

                    -- Inline Table-Valued Function
                    CREATE FUNCTION dbo.GetProductsByCategory (@CategoryName VARCHAR(50))
                    RETURNS TABLE
                    AS
                    RETURN
                    (
                        SELECT ProductID, ProductName, Price
                        FROM Products
                        WHERE Category = @CategoryName
                    );
                

Usage:

SELECT * FROM dbo.GetProductsByCategory('Beverages');

Experiment with these functions to enhance your SQL queries and build more powerful database solutions.