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
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
TRIMandREPLACEclean text up;CHARINDEX,SUBSTRING,LEFTandRIGHTtake it apart. T-SQL counts characters from 1, where C# counts from 0.CEILING(39 / 12.0)is 4 cartons, butCEILING(39 / 12)is 3: whole numbers divide first and drop the remainder.ROUND(2.5, 0)gives 3.DATEADD,EOMONTHandDATENAMEmove and name dates;DATEDIFFcounts 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.ISNULLcan cut its stand-in short;COALESCEcan'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-7788has to become1715557788. - 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:
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.
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 wherefindfirst appears intext.LEFT(text, n)gives the firstncharacters, andRIGHT(text, n)the lastn.SUBSTRING(text, start, length)giveslengthcharacters, starting at positionstart.
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.
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:
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.
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.
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?
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:
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.
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:
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:
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, thenDial. Dialis thePhonewith 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), Microsoft Learn: the list of text functions.
- Microsoft, TRIM (Transact-SQL), LEN (Transact-SQL) (trailing spaces aren't counted), REPLACE (Transact-SQL) (compares by the collation), CHARINDEX (Transact-SQL) (0 when not found) and SUBSTRING (Transact-SQL), Microsoft Learn.
- Microsoft, CEILING (Transact-SQL), FLOOR (Transact-SQL), ROUND (Transact-SQL) (negative places, the third argument) and ABS (Transact-SQL), Microsoft Learn.
- Microsoft, Math.Round Method, .NET API reference: C# rounds halves to the even number by default.
- Microsoft, DATEDIFF (Transact-SQL) (it counts boundaries crossed), DATEADD (Transact-SQL) (the end of a month), DATENAME (Transact-SQL), DATEPART (Transact-SQL) and EOMONTH (Transact-SQL), Microsoft Learn.
- Microsoft, CAST and CONVERT (Transact-SQL) and FORMAT (Transact-SQL) (.NET format strings, text, a culture), Microsoft Learn.
- Microsoft, NULLIF (Transact-SQL), ISNULL (Transact-SQL) (the first value's type) and COALESCE (Transact-SQL) (how it differs from ISNULL), Microsoft Learn.
- Microsoft, Collation and Unicode support, Microsoft Learn: what
CI(case-insensitive) means.
Key terms
- String Functions
- Date Functions
- CAST and CONVERT
- Collation