# More T-SQL Functions

The T-SQL functions a developer reaches for every week, grouped by job. Clean up and take apart text, round numbers, move and format dates, and keep NULL from surprising you.

Apprentice · T-SQL · SQL · Updated November 16, 2026

By Gregor Dzierzon

## After this lesson you can

- Clean up and take apart text in T-SQL with TRIM, REPLACE, CHARINDEX and SUBSTRING
- Round numbers and work with dates in T-SQL with CEILING, FLOOR, DATEADD, DATENAME, EOMONTH and FORMAT
- Avoid NULL surprises in T-SQL with NULLIF, and choose between ISNULL and COALESCE

## Key takeaways

- `TRIM` and `REPLACE` clean text up; `CHARINDEX`, `SUBSTRING`, `LEFT` and `RIGHT` take it apart. T-SQL counts characters from 1, where C# counts from 0.
- `CEILING(39 / 12.0)` is 4 cartons, but `CEILING(39 / 12)` is 3: whole numbers divide first and drop the remainder. `ROUND(2.5, 0)` gives 3.
- `DATEADD`, `EOMONTH` and `DATENAME` move and name dates; `DATEDIFF` counts boundaries crossed, so it's one year from December 31 to January 1.
- `FORMAT(OrderDate, 'MMMM d, yyyy')` gives text like July 4, 2016, with C#'s format strings. It's text, so sort by the date itself.
- `NULLIF(UnitsInStock, 0)` turns a 0 into NULL, so dividing by it gives NULL instead of an error. `ISNULL` can cut its stand-in short; `COALESCE` can't.

*Common T-SQL Functions* met the first few built-in functions: `CONCAT`, `UPPER`, `LEN`, `COALESCE`, `ISNULL`, `YEAR`, `DATEDIFF` and `ROUND`. Real requests soon ask for more. Here are four that could land on a Northwind developer's desk in a single week:

- The new phone system wants each customer's phone number as **digits only**: `(171) 555-7788` has to become `1715557788`.
- The email tool wants each contact's **first name**, so a message can start "Hi Maria".
- The warehouse packs **12 units to a carton** and wants to know how many cartons each product needs.
- Accounting sends invoices **due 30 days after the order** and wants the due date printed as `August 3, 2016`.

You could read every row into C# and fix each value there, with the methods from *Useful String Methods*. But SQL Server can hand you the finished values, and then every program that runs the query gets the same answer. This lesson goes deeper into the functions a junior developer uses every week, grouped by the job they do. Like the ones you've met, they work on **one row at a time**: each row gets its own answer, and the table itself never changes.

## Clean up text: TRIM and REPLACE

A customer types *Chai* into the search box on the store's site, with three stray spaces before it and two after it. `WHERE ProductName = '   Chai  '` finds nothing, because to the database those spaces are characters like any other.

**`TRIM()`** removes the spaces at both ends of a piece of text. The square brackets are there only so you can see that no spaces are left inside them:

```sql
SELECT '[' + TRIM('   Chai  ') + ']' AS Trimmed;
```

| Trimmed |
|---|
| [Chai] |

When only one end should be cleaned, `LTRIM()` removes the spaces on the left (the start) and keeps the ones at the end, and `RTRIM()` removes the ones on the right (the end) and keeps the ones at the start. `TRIM` never touches the spaces in the middle: `TRIM(' Aniseed Syrup ')` keeps the space between the two words.

Use it on what people type, before you compare it or save it. In *Working with User Input* the search value arrived as a parameter, so the query can clean it as it compares: `WHERE ProductName = TRIM(@ProductName)`.

One surprise while you're measuring: **`LEN` doesn't count spaces at the end**. `LEN('Chai   ')` is `4`, but `LEN('   Chai')` is `7`, because the spaces at the start do count. So `LEN` can't tell you whether a value has trailing spaces to trim.

**`REPLACE(text, find, put_instead)`** swaps every place `find` appears for `put_instead`. Say each product needs a web address, such as `/products/aniseed-syrup`: lower case, with a dash in place of each space.

```sql
SELECT TOP (4) ProductName
    , LOWER(REPLACE(ProductName, ' ', '-')) AS Slug
FROM Products
ORDER BY ProductId;
```

| ProductName | Slug |
|---|---|
| Chai | chai |
| Chang | chang |
| Aniseed Syrup | aniseed-syrup |
| Chef Anton's Cajun Seasoning | chef-anton's-cajun-seasoning |

This is a **nested** call: one function inside another. Read it from the inside out. `REPLACE` runs first and gives `Aniseed-Syrup`; then `LOWER` turns that into `aniseed-syrup`. The apostrophe in *Anton's* is still there. Another `REPLACE` wrapped around the outside could take it out too: you can nest as many as the job needs.

### Letter case in comparisons

SQL Server compares text the way its **collation** says: the set of rules for sorting and comparing text that each database is created with. Northwind's is `SQL_Latin1_General_CP1_CI_AS`, and `CI` means *case-insensitive*. So `WHERE Country = 'germany'` finds the customers in `Germany`, and even `REPLACE('Chai', 'c', 'k')` gives `khai`, because the capital `C` counts as a match. `UPPER` and `LOWER` are for what people see, such as a country printed in capitals on a shipping label, not for making comparisons work. (Other databases, MySQL's `REPLACE` among them, match letter case exactly, so code that moves between databases shouldn't count on this.)

## Take text apart: CHARINDEX, SUBSTRING, LEFT and RIGHT

Back to the email tool, which wants each contact's first name. In *Useful String Methods* you did this in C#: find the space with `IndexOf(' ')`, then take everything before it with `Substring(0, position)`. T-SQL has a function for each step.

- **`CHARINDEX(find, text)`** gives the position where `find` first appears in `text`.
- **`LEFT(text, n)`** gives the first `n` characters, and **`RIGHT(text, n)`** the last `n`.
- **`SUBSTRING(text, start, length)`** gives `length` characters, starting at position `start`.

```sql
SELECT TOP (4) ContactName
    , CHARINDEX(' ', ContactName) AS SpaceAt
    , LEFT(ContactName, CHARINDEX(' ', ContactName) - 1) AS FirstName
    , SUBSTRING(ContactName, CHARINDEX(' ', ContactName) + 1, LEN(ContactName)) AS LastName
FROM Customers
ORDER BY CustomerId;
```

| ContactName | SpaceAt | FirstName | LastName |
|---|---|---|---|
| Maria Anders | 6 | Maria | Anders |
| Ana Trujillo | 4 | Ana | Trujillo |
| Antonio Moreno | 8 | Antonio | Moreno |
| Thomas Hardy | 7 | Thomas | Hardy |

**T-SQL counts characters from 1, not from 0.** In `Maria Anders`, the `M` is at position 1 and the space is at position 6, so the first name is the 5 characters before it: `LEFT(ContactName, 6 - 1)`. C#'s `IndexOf(' ')` would say 5 for the same space, because C# starts counting at 0.

`SUBSTRING` needs a length. To take "everything after the space", the query asks for `LEN(ContactName)` characters: more than are left, which is fine, because `SUBSTRING` stops at the end of the text.

| Call | Gives | Why |
|---|---|---|
| `SUBSTRING('Northwind', 1, 5)` | `North` | 5 characters, starting at the first |
| `SUBSTRING('Northwind', 6, 4)` | `wind` | 4 characters, starting at position 6 |
| `SUBSTRING('Northwind', 0, 5)` | `Nort` | position 0 is before the text, so it counts as one of the 5 |
| `RIGHT('(171) 555-7788', 4)` | `7788` | the last 4 characters |
| `CHARINDEX('z', 'Northwind')` | `0` | not found |

When the text isn't there at all, `CHARINDEX` gives `0`, where C#'s `IndexOf` gives `-1`. That matters for the first-name query: a contact with only one name, such as `Cher`, has no space, so `CHARINDEX` gives `0`, and `LEFT(ContactName, 0 - 1)` stops the whole query with *Invalid length parameter passed to the left function*. Every contact in Northwind has a space, so the query runs; with real data, check for the space first.

Names are messy in another way too. One Northwind contact is `Isabel de Castro`: everything after the first space is `de Castro`, which is right for her. Decide what the job needs before you choose the function.

## Round numbers: CEILING, FLOOR, ROUND and ABS

The warehouse packs 12 units to a carton. Chai has 39 units in stock: how many cartons is that? In *Arithmetic and Expressions in C#* you'd write `Math.Ceiling(39 / 12.0)`. T-SQL has the same two functions:

- **`CEILING()`** rounds **up** to the next whole number: the cartons you need to hold every unit.
- **`FLOOR()`** rounds **down**: the cartons you can fill completely.

```sql
SELECT TOP (5) ProductName
    , UnitsInStock
    , UnitsInStock / 12 AS FullCartons
    , CEILING(UnitsInStock / 12.0) AS CartonsNeeded
    , FLOOR(UnitsInStock / 12.0) AS FloorCartons
FROM Products
ORDER BY ProductId;
```

| ProductName | UnitsInStock | FullCartons | CartonsNeeded | FloorCartons |
|---|---|---|---|---|
| Chai | 39 | 3 | 4 | 3 |
| Chang | 17 | 1 | 2 | 1 |
| Aniseed Syrup | 13 | 1 | 2 | 1 |
| Chef Anton's Cajun Seasoning | 53 | 4 | 5 | 4 |
| Chef Anton's Gumbo Mix | 0 | 0 | 0 | 0 |

Look at the `12.0`. As *Calculations in Columns* showed, dividing one whole number by another drops the remainder, and that happens **before** `CEILING` sees the number. `CEILING(39 / 12)` is `CEILING(3)`, which is `3`: one carton short. `CEILING(39 / 12.0)` is `CEILING(3.25)`, which is `4`. `UnitsInStock / 12` on its own does the job `FLOOR` does, as the `FullCartons` column shows.

**`ROUND(number, places)`** goes deeper than *Common T-SQL Functions* showed. A negative number of places rounds to the left of the decimal point, to tens or hundreds, as a sales report does with "about $700". A third argument of `1` cuts the extra digits off instead of rounding. **`ABS()`** gives a number without its minus sign: its distance from zero.

| Call | Gives | Why |
|---|---|---|
| `ROUND(18.4567, 2)` | `18.4600` | the 6 after 18.45 rounds it up; the places stay |
| `ROUND(18.4567, 2, 1)` | `18.4500` | the `1` cuts off instead of rounding |
| `ROUND(2.5, 0)` | `3.0` | a half rounds away from zero |
| `ROUND(1234.567, -2)` | `1200.000` | -2 places: to the nearest hundred |
| `ROUND(702.00, -2)` | `700.00` | Chai's stock value, about $700 |
| `ABS(-16)` | `16` | the distance from zero |

The half rule differs from C#: `Math.Round(2.5)` gives `2`, because C# rounds halves to the even number unless you tell it otherwise. So a total rounded in SQL and the same total rounded in C# can be a cent apart. Pick one place to round, and round there.

## Work with dates

### Today, without the time

`GETDATE()` gives the date and time on the server, such as `2026-11-16 09:25:46.270`. (Standard SQL spells it `CURRENT_TIMESTAMP`, which SQL Server also understands.) Often you want **today**, without the time: the orders placed today, a report dated today. **`CAST(value AS type)`** turns a value into another type, and the `date` type holds a date with no time:

```sql
SELECT CAST(GETDATE() AS date) AS Today;
```

Run on November 16, 2026, it gave `2026-11-16`. "The orders placed in the last 30 days" is about days, so it starts from today, not from this moment: starting from `GETDATE()` at 9:25 in the morning would leave out the orders placed earlier on that first day.

```sql
SELECT OrderId, OrderDate
FROM Orders
WHERE OrderDate >= DATEADD(day, -30, CAST(GETDATE() AS date));
```

On Northwind it returns no rows: its orders run from 2016-07-04 to 2018-05-06.

### Names and parts of a date

*Common T-SQL Functions* pulled numbers out of a date with `YEAR`, `MONTH` and `DAY`. **`DATENAME(part, date)`** gives a part's **name**, and **`DATEPART(part, date)`** gives any part as a number, the quarter of the year included, which sales reports group by.

```sql
SELECT TOP (3) OrderId
    , OrderDate
    , DATENAME(weekday, OrderDate) AS DayName
    , DATENAME(month, OrderDate) AS MonthName
    , DATEPART(quarter, OrderDate) AS Quarter
FROM Orders
ORDER BY OrderId;
```

| OrderId | OrderDate | DayName | MonthName | Quarter |
|---|---|---|---|---|
| 10248 | 2016-07-04 00:00:00.000 | Monday | July | 3 |
| 10249 | 2016-07-05 00:00:00.000 | Tuesday | July | 3 |
| 10250 | 2016-07-08 00:00:00.000 | Friday | July | 3 |

### Late orders: DATEDIFF and ABS

Every order has a `RequiredDate`, the day the customer was promised it, and a `ShippedDate`. Which orders shipped late, and by how many days?

```sql
SELECT TOP (5) OrderId
    , RequiredDate
    , ShippedDate
    , DATEDIFF(day, RequiredDate, ShippedDate) AS DaysLate
FROM Orders
WHERE ShippedDate > RequiredDate
ORDER BY OrderId;
```

| OrderId | RequiredDate | ShippedDate | DaysLate |
|---|---|---|---|
| 10264 | 2016-08-21 00:00:00.000 | 2016-08-23 00:00:00.000 | 2 |
| 10271 | 2016-08-29 00:00:00.000 | 2016-08-30 00:00:00.000 | 1 |
| 10280 | 2016-09-11 00:00:00.000 | 2016-09-12 00:00:00.000 | 1 |
| 10302 | 2016-10-08 00:00:00.000 | 2016-10-09 00:00:00.000 | 1 |
| 10309 | 2016-10-17 00:00:00.000 | 2016-10-23 00:00:00.000 | 6 |

An order that shipped early gives a negative number: order 10248 was promised for 2016-08-01 and shipped on 2016-07-16, so its `DaysLate` is `-16`. When the shipping manager wants "how many days off the promised date, early or late", `ABS` drops the sign: `ABS(DATEDIFF(day, RequiredDate, ShippedDate))` is `16`.

Labeling each order "Late" or "On time" by a rule is a job for `CASE`, which *CASE / WHEN in T-SQL* teaches later in the course.

### DATEDIFF counts boundaries

`DATEDIFF` doesn't measure whole years or months: it counts how many times the calendar crosses a boundary between the two dates. That's exact for days, and it surprises people for years:

| Call | Gives | Why |
|---|---|---|
| `DATEDIFF(year, '2017-12-31', '2018-01-01')` | `1` | one day apart, but a new year started |
| `DATEDIFF(month, '2018-01-31', '2018-02-01')` | `1` | one day apart, but a new month started |
| `DATEDIFF(year, '2000-12-08', '2026-11-16')` | `26` | the 26th birthday, on December 8, hasn't come yet |

So `DATEDIFF(year, BirthDate, GETDATE())` is a year too old for anyone whose birthday hasn't come yet this year. For an age that has to be right, work it out in C#, which can compare the birthday itself.

### Moving a date: DATEADD and EOMONTH

*Common T-SQL Functions* moved a date with `DATEADD(day, n, date)`. The invoice due date, 30 days after the order, and the last day of the order's month, for invoices due at the end of the month:

```sql
SELECT TOP (3) OrderId
    , OrderDate
    , DATEADD(day, 30, OrderDate) AS PaymentDue
    , EOMONTH(OrderDate) AS MonthEnd
FROM Orders
ORDER BY OrderId;
```

| OrderId | OrderDate | PaymentDue | MonthEnd |
|---|---|---|---|
| 10248 | 2016-07-04 00:00:00.000 | 2016-08-03 00:00:00.000 | 2016-07-31 |
| 10249 | 2016-07-05 00:00:00.000 | 2016-08-04 00:00:00.000 | 2016-07-31 |
| 10250 | 2016-07-08 00:00:00.000 | 2016-08-07 00:00:00.000 | 2016-07-31 |

**`EOMONTH(date)`** ("end of month") gives the last day of the date's month. Moving by months has its own rule: when the new month is too short, `DATEADD` stops at its last day. `DATEADD(month, 1, '2018-01-31')` gives `2018-02-28`, and `DATEADD(day, -7, '2018-03-02')` moves a week back, to `2018-02-23`.

### Dates and numbers for people: FORMAT

**`FORMAT(value, pattern)`** turns a date or a number into text, with the same format strings as C#'s `ToString`: `yyyy` the year, `MM` the month's number, `MMMM` its name, `d` the day, `ddd` the short weekday name. A third argument names the culture, such as `'en-US'`, which decides the month names and the money sign.

```sql
SELECT TOP (3) OrderId
    , FORMAT(OrderDate, 'yyyy-MM-dd') AS IsoDate
    , FORMAT(OrderDate, 'MMMM d, yyyy') AS LongDate
    , FORMAT(OrderDate, 'ddd MMM d') AS ShortDate
    , FORMAT(Freight, 'C', 'en-US') AS FreightText
FROM Orders
ORDER BY OrderId;
```

| OrderId | IsoDate | LongDate | ShortDate | FreightText |
|---|---|---|---|---|
| 10248 | 2016-07-04 | July 4, 2016 | Mon Jul 4 | $32.38 |
| 10249 | 2016-07-05 | July 5, 2016 | Tue Jul 5 | $11.61 |
| 10250 | 2016-07-08 | July 8, 2016 | Fri Jul 8 | $65.83 |

Two cautions. The answer is **text**, not a date: sort by `LongDate` and `April` comes before `July`, because *A* comes before *J*, so sort by the date column itself. And `FORMAT` is slow on many rows. *Common T-SQL Functions* said to format numbers for people in your C# code, and that still holds for an app's pages. `FORMAT` earns its place in a report or a file you export straight from the database.

## NULL surprises: NULLIF, and ISNULL beside COALESCE

### Dividing by zero

The buyer compares what's on order with what's on the shelf: Aniseed Syrup has 13 on the shelf and 70 coming, more than five times as much. The obvious query is `UnitsOnOrder * 1.0 / UnitsInStock`. But Gorgonzola Telino has 0 in stock and 70 on order, and SQL Server stops the **whole query** with *Divide by zero error encountered*. One row with a 0 and nobody gets an answer.

**`NULLIF(a, b)`** gives `NULL` when its two values are equal, and `a` when they aren't. So `NULLIF(UnitsInStock, 0)` turns a 0 into `NULL`, and dividing by `NULL` gives `NULL` instead of an error:

```sql
SELECT TOP (5) ProductName
    , UnitsInStock
    , UnitsOnOrder
    , UnitsOnOrder * 1.0 / NULLIF(UnitsInStock, 0) AS OrderToStock
FROM Products
WHERE UnitsOnOrder > 0
ORDER BY ProductId;
```

| ProductName | UnitsInStock | UnitsOnOrder | OrderToStock |
|---|---|---|---|
| Chang | 17 | 40 | 2.3529411 |
| Aniseed Syrup | 13 | 70 | 5.3846153 |
| Queso Cabrales | 22 | 30 | 1.3636363 |
| Sir Rodney's Scones | 3 | 40 | 13.3333333 |
| Gorgonzola Telino | 0 | 70 | NULL |

`NULL` is the honest answer here: there's no ratio to nothing. Wrap it in `COALESCE` if the report needs a word instead.

### Empty text that should count as NULL

A form that saves customers sometimes stores an empty fax number as `''`, empty text, instead of `NULL`. Then `COALESCE(Fax, 'none')` shows `none` for some customers with no fax and a blank for others, because `''` isn't `NULL`. `NULLIF` fixes that too: `NULLIF(Fax, '')` turns empty text into `NULL`, and `COALESCE` does the rest.

| Fax | `NULLIF(Fax, '')` | `COALESCE(NULLIF(Fax, ''), 'none')` |
|---|---|---|
| `030-0076545` | `030-0076545` | `030-0076545` |
| `''` (empty) | `NULL` | `none` |
| `NULL` | `NULL` | `none` |

### ISNULL can cut its stand-in short

*Common T-SQL Functions* said `ISNULL` and `COALESCE` both supply a stand-in. There's a difference that bites: **`ISNULL` gives back the type of its first value**. `Region` is an `nvarchar(15)`, text of at most 15 characters, so a longer stand-in is cut to fit:

```sql
SELECT TOP (3) CompanyName
    , ISNULL(Region, 'No region on file') AS WithIsNull
    , COALESCE(Region, 'No region on file') AS WithCoalesce
FROM Customers
ORDER BY CustomerId;
```

| CompanyName | WithIsNull | WithCoalesce |
|---|---|---|
| Alfreds Futterkiste | No region on fi | No region on file |
| Ana Trujillo Emparedados y helados | No region on fi | No region on file |
| Antonio Moreno Taquería | No region on fi | No region on file |

`COALESCE` picks a type that fits all of its values, so the whole stand-in survives. Reach for `COALESCE` first: it's standard SQL, it takes any number of values, and it doesn't cut text short. `ISNULL` is fine when the stand-in is the same type and size as the column, such as `ISNULL(UnitsOnOrder, 0)`.

## Which function for which job

| The job | T-SQL | Example | Gives |
|---|---|---|---|
| Remove spaces at the ends | `TRIM` | `TRIM('  Chai ')` | `Chai` |
| Swap one piece of text for another | `REPLACE` | `REPLACE('555-7788', '-', '')` | `5557788` |
| Find a position | `CHARINDEX` | `CHARINDEX(' ', 'Maria Anders')` | `6` |
| First or last characters | `LEFT`, `RIGHT` | `RIGHT('030-0074321', 4)` | `4321` |
| A piece from the middle | `SUBSTRING` | `SUBSTRING('Northwind', 6, 4)` | `wind` |
| Round up, round down | `CEILING`, `FLOOR` | `CEILING(39 / 12.0)` | `4` |
| Round to places | `ROUND` | `ROUND(1234.567, -2)` | `1200.000` |
| Distance from zero | `ABS` | `ABS(-16)` | `16` |
| Today, without the time | `CAST(GETDATE() AS date)` | | today's date |
| A part's name | `DATENAME` | `DATENAME(weekday, '2016-07-04')` | `Monday` |
| Move a date | `DATEADD` | `DATEADD(month, 1, '2018-01-31')` | `2018-02-28` |
| The month's last day | `EOMONTH` | `EOMONTH('2016-07-04')` | `2016-07-31` |
| A date as text | `FORMAT` | `FORMAT(OrderDate, 'MMMM d, yyyy')` | `July 4, 2016` |
| A value as NULL | `NULLIF` | `NULLIF(0, 0)` | `NULL` |
| A stand-in for NULL | `COALESCE`, `ISNULL` | `COALESCE(NULL, 'n/a')` | `n/a` |

## Do it: dial the customers' numbers

The new phone system dials digits only. Each customer's `Phone` was typed in its own country's style, with parentheses, spaces, dashes and dots, and the system can't dial any of them.

The `Customers` table (the **Tables** tab shows the SQL that makes it) has these rows:

| CompanyName | Phone |
|---|---|
| Alfreds Futterkiste | 030-0074321 |
| Around the Horn | (171) 555-7788 |
| Bon app' | 91.24.45.40 |
| Bottom-Dollar Markets | (604) 555-4729 |
| Romero y tomillo | (91) 745 6200 |
| Simons bistro | 31 12 34 56 |

For example, Around the Horn's `(171) 555-7788` loses its `(`, its `)`, its space and its dash, and becomes `1715557788`. The digits stay text, so a number that starts with `0`, like Alfreds Futterkiste's, keeps its `0`.

Your query should give exactly these rows, in this order:

| CompanyName | Dial |
|---|---|
| Alfreds Futterkiste | 0300074321 |
| Around the Horn | 1715557788 |
| Bon app' | 91244540 |
| Bottom-Dollar Markets | 6045554729 |
| Romero y tomillo | 917456200 |
| Simons bistro | 31123456 |

**Your job:** write one `SELECT` query that gives each customer's number, ready to dial.

- Show two columns: `CompanyName`, then `Dial`.
- `Dial` is the `Phone` with every `(`, `)`, space, `-` and `.` taken out, and nothing else changed.
- Use `REPLACE`. It works the same in SQL Server, MySQL and SQLite, the database this practice box runs.
- 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

These functions turn the messy values real tables hold into the clean ones your C# code and your reports need: digits ready to dial, first names, whole cartons, due dates. Knowing their surprises (the whole-number division inside `CEILING`, `DATEDIFF`'s boundaries, `ISNULL`'s short stand-in) is what keeps a report from being quietly wrong.

## Sources

- Microsoft, [String functions (Transact-SQL)](https://learn.microsoft.com/en-us/sql/t-sql/functions/string-functions-transact-sql), Microsoft Learn: the list of text functions.
- Microsoft, [TRIM (Transact-SQL)](https://learn.microsoft.com/en-us/sql/t-sql/functions/trim-transact-sql), [LEN (Transact-SQL)](https://learn.microsoft.com/en-us/sql/t-sql/functions/len-transact-sql) (trailing spaces aren't counted), [REPLACE (Transact-SQL)](https://learn.microsoft.com/en-us/sql/t-sql/functions/replace-transact-sql) (compares by the collation), [CHARINDEX (Transact-SQL)](https://learn.microsoft.com/en-us/sql/t-sql/functions/charindex-transact-sql) (0 when not found) and [SUBSTRING (Transact-SQL)](https://learn.microsoft.com/en-us/sql/t-sql/functions/substring-transact-sql), Microsoft Learn.
- Microsoft, [CEILING (Transact-SQL)](https://learn.microsoft.com/en-us/sql/t-sql/functions/ceiling-transact-sql), [FLOOR (Transact-SQL)](https://learn.microsoft.com/en-us/sql/t-sql/functions/floor-transact-sql), [ROUND (Transact-SQL)](https://learn.microsoft.com/en-us/sql/t-sql/functions/round-transact-sql) (negative places, the third argument) and [ABS (Transact-SQL)](https://learn.microsoft.com/en-us/sql/t-sql/functions/abs-transact-sql), Microsoft Learn.
- Microsoft, [Math.Round Method](https://learn.microsoft.com/en-us/dotnet/api/system.math.round), .NET API reference: C# rounds halves to the even number by default.
- Microsoft, [DATEDIFF (Transact-SQL)](https://learn.microsoft.com/en-us/sql/t-sql/functions/datediff-transact-sql) (it counts boundaries crossed), [DATEADD (Transact-SQL)](https://learn.microsoft.com/en-us/sql/t-sql/functions/dateadd-transact-sql) (the end of a month), [DATENAME (Transact-SQL)](https://learn.microsoft.com/en-us/sql/t-sql/functions/datename-transact-sql), [DATEPART (Transact-SQL)](https://learn.microsoft.com/en-us/sql/t-sql/functions/datepart-transact-sql) and [EOMONTH (Transact-SQL)](https://learn.microsoft.com/en-us/sql/t-sql/functions/eomonth-transact-sql), Microsoft Learn.
- Microsoft, [CAST and CONVERT (Transact-SQL)](https://learn.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql) and [FORMAT (Transact-SQL)](https://learn.microsoft.com/en-us/sql/t-sql/functions/format-transact-sql) (.NET format strings, text, a culture), Microsoft Learn.
- Microsoft, [NULLIF (Transact-SQL)](https://learn.microsoft.com/en-us/sql/t-sql/language-elements/nullif-transact-sql), [ISNULL (Transact-SQL)](https://learn.microsoft.com/en-us/sql/t-sql/functions/isnull-transact-sql) (the first value's type) and [COALESCE (Transact-SQL)](https://learn.microsoft.com/en-us/sql/t-sql/language-elements/coalesce-transact-sql) (how it differs from ISNULL), Microsoft Learn.
- Microsoft, [Collation and Unicode support](https://learn.microsoft.com/en-us/sql/relational-databases/collations/collation-and-unicode-support), Microsoft Learn: what `CI` (case-insensitive) means.

## Key terms

- String Functions
- Date Functions
- CAST and CONVERT
- Collation

## Cheat sheets

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

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