# 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 November 3, 2026

## 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`**:

```sql
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:

```sql
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:

```sql
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:

```sql
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

```sql
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 |

```sql
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`:

```sql
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, `NULL`s 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

- [Common T-SQL Functions](https://www.ask-codey.com/cheat-sheets/common-t-sql-functions-cheat-sheet)

Source: https://www.ask-codey.com/lessons/common-t-sql-functions