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

By

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:

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 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.
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, 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

Key terms

  • String Functions
  • Date Functions
  • CAST and CONVERT
  • Collation

Cheat sheets