What is Scalar function examples

At the time of writing, the SQL Server Technical Documentation listed more than 200 scalar functions that span multiple categories, including:

• Configuration functions
• Conversion functions
• Cursor functions
• Date and Time functions
• Mathematical functions
• Metadata functions
• Security functions
• String functions
• System functions
• System Statistical functions
• Text and Image functions

There isn’t enough time in this course to describe each function, but the examples below show some commonly used functions.

The following hypothetical example uses several date and time functions:

SQLCopy

SELECT  SalesOrderID,
    OrderDate,
        YEAR(OrderDate) AS OrderYear,
        DATENAME(mm, OrderDate) AS OrderMonth,
        DAY(OrderDate) AS OrderDay,
        DATENAME(dw, OrderDate) AS OrderWeekDay,
        DATEDIFF(yy,OrderDate, GETDATE()) AS YearsSinceOrder
FROM Sales.SalesOrderHeader;

Partial results are shown below:

SalesOrderID

OrderDate

OrderYear

OrderMonth

OrderDay

OrderWeekDay

YearsSinceOrder

71774

2008-06-01T00:00:00

2008

June

1

Sunday

13

…

…

…

…

…

…

…

The next example includes some mathematical functions:

SQLCopy

SELECT TaxAmt,
       ROUND(TaxAmt, 0) AS Rounded,
       FLOOR(TaxAmt) AS Floor,
       CEILING(TaxAmt) AS Ceiling,
       SQUARE(TaxAmt) AS Squared,
       SQRT(TaxAmt) AS Root,
       LOG(TaxAmt) AS Log,
       TaxAmt * RAND() AS Randomized
FROM Sales.SalesOrderHeader;

Partial results:

TaxAmt

Rounded

Floor

Ceiling

Squared

Root

Log

Randomized

70.4279

70.0000

70.0000

71.0000

4960.089098

8.392133221

4.254589491

28.64120429

…

..

…

…

…

…

…

…

The following example uses some string functions:

SQLCopy

SELECT  CompanyName,
        UPPER(CompanyName) AS UpperCase,
        LOWER(CompanyName) AS LowerCase,
        LEN(CompanyName) AS Length,
        REVERSE(CompanyName) AS Reversed,
        CHARINDEX(' ', CompanyName) AS FirstSpace,
        LEFT(CompanyName, CHARINDEX(' ', CompanyName)) AS FirstWord,
        SUBSTRING(CompanyName, CHARINDEX(' ', CompanyName) + 1, LEN(CompanyName)) AS RestOfName
FROM Sales.Customer;

Partial results:

CompanyName

UpperCase

LowerCase

Length

Reversed

FirstSpace

FirstWord

RestOfName

A Bike Store

A BIKE STORE

a bike store

12

erotS ekiB A

2

A

Bike Store

Progressive Sports

PROGRESSIVE SPORTS

progressive sports

18

stropS evissergorP

12

Progressive

Sports

Advanced Bike Components

ADVANCED BIKE COMPONENTS

advanced bike components

24

stnenopmoC ekiB decnavdA

9

Advanced

Bike Components