Common T-SQL Functions

T-SQL's built-in functions work on one row at a time. Join and change text, put a value in place of NULL, and pull apart dates and round numbers.

Apprentice · T-SQL · SQL · Updated

After this lesson you can

  • Use T-SQL text functions such as CONCAT, UPPER and LEN in a query
  • Replace NULL with a value using COALESCE or ISNULL
  • Use date and number functions such as YEAR, DATEDIFF, GETDATE and ROUND

Key takeaways

  • UPPER(ProductName) gives the name in capitals and LEN(ProductName) its number of characters: functions like these give each row its own answer.
  • Joining text with + gives NULL if any part is NULL; CONCAT treats a NULL as empty text and carries on.
  • COALESCE returns the first of its values that isn't NULL; ISNULL does the same with exactly two values and is SQL Server's own.
  • YEAR(OrderDate) pulls one part out of a date, DATEDIFF(day, OrderDate, ShippedDate) counts the days between two dates, and ROUND rounds a number.
  • Functions work anywhere a value does, including WHERE: WHERE YEAR(OrderDate) = 2018 keeps the orders from 2018.

SQL Server comes with a long list of built-in functions: small tools that take a value and give back a new one. The ones in this lesson work on one row at a time: each row gets its own answer.

Text

Join text with +, as you did in Calculations in Columns, or with CONCAT:

SELECT TOP (3) CONCAT(TitleOfCourtesy, ' ', FirstName, ' ', LastName) AS FullName
FROM Employees
ORDER BY EmployeeId;
FullName
Ms. Nancy Davolio
Dr. Andrew Fuller
Ms. Janet Leverling

The difference shows up with NULL: 'Ms.' + NULL is NULL, but CONCAT treats a NULL as empty text and carries on.

A few more text functions:

SELECT TOP (3) ProductName
    , UPPER(ProductName) AS Shout
    , LEN(ProductName) AS Letters
FROM Products
ORDER BY ProductId;
ProductName Shout Letters
Chai CHAI 4
Chang CHANG 5
Aniseed Syrup ANISEED SYRUP 13
Function Returns
CONCAT(a, b, ...) The values joined into one piece of text
UPPER(text), LOWER(text) The text in capitals, or in lower case
LEN(text) How many characters the text has
LEFT(text, n), RIGHT(text, n) The first or last n characters
TRIM(text) The text without spaces at either end

A value in place of NULL

Many customers have no region: the column is NULL. COALESCE returns the first of its values that isn't NULL, so it can supply a stand-in:

SELECT TOP (4) CompanyName, Region, COALESCE(Region, 'n/a') AS RegionOrNa
FROM Customers
ORDER BY CustomerId;
CompanyName Region RegionOrNa
Alfreds Futterkiste NULL n/a
Ana Trujillo Emparedados y helados NULL n/a
Antonio Moreno Taquería NULL n/a
Around the Horn NULL n/a

COALESCE takes any number of values: COALESCE(Region, Country) gives the region, or the country when there's no region.

SQL Server also has ISNULL(value, replacement), which does the same with exactly two values:

SELECT TOP (4) CompanyName, ISNULL(Fax, 'none') AS Fax
FROM Customers
ORDER BY CustomerId;
CompanyName Fax
Alfreds Futterkiste 030-0076545
Ana Trujillo Emparedados y helados (5) 555-3745
Antonio Moreno Taquería none
Around the Horn (171) 555-6750

COALESCE is standard SQL and works in every database; ISNULL is SQL Server's own.

Dates and numbers

SELECT TOP (3) OrderId
    , OrderDate
    , YEAR(OrderDate) AS OrderYear
    , DATEDIFF(day, OrderDate, ShippedDate) AS DaysToShip
FROM Orders
ORDER BY OrderId;
OrderId OrderDate OrderYear DaysToShip
10248 2016-07-04 00:00:00.000 2016 12
10249 2016-07-05 00:00:00.000 2016 5
10250 2016-07-08 00:00:00.000 2016 4
Function Returns
GETDATE() The date and time now, on the server
YEAR(date), MONTH(date), DAY(date) One part of a date, as a number
DATEDIFF(day, start, end) How many days from start to end (or month, year, hour...)
DATEADD(day, n, date) The date n days later (or months, years...)
ROUND(number, places) The number rounded to that many decimal places 
SELECT TOP (3) ProductName, UnitPrice, ROUND(UnitPrice * 1.075, 2) AS WithTax
FROM Products
ORDER BY ProductId;
ProductName UnitPrice WithTax
Chai 18.00 19.3500000
Chang 19.00 20.4300000
Aniseed Syrup 10.00 10.7500000

ROUND rounds the value, but keeps the type's decimal places, so the extra zeros stay. That's fine: format numbers for people in your C# code, not in SQL.

Functions in WHERE

Functions work anywhere a value does, including WHERE:

SELECT OrderId, OrderDate
FROM Orders
WHERE YEAR(OrderDate) = 2018 AND MONTH(OrderDate) = 5;

Check yourself

3 questions to try on the lesson page.

So what

Built-in functions save you from doing in C# what SQL can do on the way out: names joined, NULLs filled in, dates pulled apart. They work row by row; the next lesson meets functions that work across many rows at once.

Key terms

  • String Functions
  • COALESCE
  • ISNULL
  • Date Functions

Cheat sheets