SQL Scalar Functions

Scalar functions return a single value. They can be used in expressions, WHERE clauses, and ORDER BY clauses. SQL Server provides a rich set of built-in scalar functions for various purposes, including string manipulation, date and time operations, mathematical calculations, and data type conversions.

Categories of Scalar Functions

String Functions

Manipulate character strings.

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

More...

Date and Time Functions

Work with date and time values.

GETDATE()
DATEPART()
DATEDIFF()
DATEADD()
FORMAT()
YEAR()
MONTH()
DAY()

More...

Mathematical Functions

Perform mathematical operations.

ABS()
CEILING()
FLOOR()
ROUND()
SQRT()
POWER()
RAND()
PI()

More...

Conversion Functions

Convert values from one data type to another.

CAST()
CONVERT()
PARSE()
TRY_CAST()
TRY_CONVERT()
TRY_PARSE()

More...

Aggregate Functions (as Scalar)

Can sometimes be used to return a single value.

COUNT()
SUM()
AVG()
MIN()
MAX()

More...

System Functions

Provide information about the system or current context.

DB_NAME()
USER_NAME()
@@ROWCOUNT
@@SERVERNAME

More...

Example Usage

Here's an example demonstrating the use of the UPPER() and GETDATE() functions:


SELECT
    UPPER(FirstName) AS UppercaseFirstName,
    GETDATE() AS CurrentDateTime
FROM
    Customers
WHERE
    CustomerID = 1;

Creating User-Defined Scalar Functions

In addition to built-in functions, you can create your own scalar functions using Transact-SQL (T-SQL) or .NET languages. User-defined scalar functions allow you to encapsulate complex logic into reusable units.

To create a user-defined scalar function, use the CREATE FUNCTION statement.


CREATE FUNCTION dbo.CalculateDiscount
(
    @Price DECIMAL(10, 2),
    @DiscountRate DECIMAL(4, 2)
)
RETURNS DECIMAL(10, 2)
AS
BEGIN
    RETURN @Price * (1 - @DiscountRate);
END;

You can then use this function in your queries:


SELECT
    ProductName,
    Price,
    dbo.CalculateDiscount(Price, 0.10) AS DiscountedPrice
FROM
    Products;