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 andLEN(ProductName)its number of characters: functions like these give each row its own answer.- Joining text with
+givesNULLif any part isNULL;CONCATtreats aNULLas empty text and carries on. COALESCEreturns the first of its values that isn'tNULL;ISNULLdoes 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, andROUNDrounds a number.- Functions work anywhere a value does, including
WHERE:WHERE YEAR(OrderDate) = 2018keeps 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;
Do it: give every customer a place on the label
The shipping team prints a label for each customer, with a place to sort it by: the customer's region, such as OR for Oregon. Some customers have no region (it's NULL). For them, the label should show the country instead.
The Customers table (the Tables tab shows the SQL that makes it) has these rows:
| CompanyName | City | Region | Country |
|---|---|---|---|
| Alfreds Futterkiste | Berlin | NULL | Germany |
| Around the Horn | London | NULL | UK |
| Bottom-Dollar Markets | Tsawassen | BC | Canada |
| Great Lakes Food Market | Eugene | OR | USA |
| Island Trading | Cowes | Isle of Wight | UK |
Your query should give exactly these rows, in this order:
| CompanyName | Place |
|---|---|
| Alfreds Futterkiste | Germany |
| Around the Horn | UK |
| Bottom-Dollar Markets | BC |
| Great Lakes Food Market | OR |
| Island Trading | Isle of Wight |
Your job: write one SELECT query that gives each customer's place.
- Show two columns:
CompanyName, thenPlace. Placeis theRegion, or theCountrywhen theRegionisNULL.- Use
COALESCE, which works in every database. (ISNULLis SQL Server's own, and doesn't run here.) - Sort the rows by
CompanyName, A to Z.
Practiced live on the lesson page: write the SQL right there and check it.
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