String, date and conversion functions
SQL Server has built-in functions to clean text, work with dates and convert between types. This lesson covers the ones you will reach for every week, from TRIM and CONCAT to DATEADD, EOMONTH and TRY_CONVERT, and warns about using functions on indexed columns in WHERE.
- Clean textTRIM, UPPER, REPLACE, SUBSTRING
- Join textCONCAT, CONCAT_WS, STRING_AGG
- DatesDATEADD, DATEDIFF, EOMONTH, DATEFROMPARTS
- Convert safelyTRY_CONVERT and TRY_CAST return NULL on bad data
- Missing valuesCOALESCE or ISNULL
- Fast filtersRanges on raw columns, not functions
Text functions
- LEN, UPPER, LOWER, TRIM, LEFT, RIGHT and SUBSTRING for basic cleanup and slicing.
- CONCAT and CONCAT_WS join values and treat NULL as an empty string.
- REPLACE swaps text; CHARINDEX finds the position of text inside text.
- STRING_AGG joins many rows into one string; STRING_SPLIT turns a list into rows.
Example: tidy customer names and list their orders
SELECT c.CustomerId,
CONCAT_WS(' ', TRIM(c.FirstName), TRIM(c.LastName)) AS FullName,
UPPER(LEFT(c.Country, 2)) AS CountryCode,
STRING_AGG(CAST(o.OrderId AS varchar(10)), ', ')
WITHIN GROUP (ORDER BY o.OrderId) AS OrderIds
FROM Customers c
LEFT JOIN Orders o ON o.CustomerId = c.CustomerId
GROUP BY c.CustomerId, c.FirstName, c.LastName, c.Country;Date functions
- SYSUTCDATETIME() for the current UTC time; store UTC and convert for display.
- DATEADD(day, 7, @d) adds time; DATEDIFF(day, @a, @b) counts boundaries between dates.
- DATEPART and DATENAME read parts; EOMONTH(@d) gives the last day of the month.
- DATEFROMPARTS(2026, 10, 1) builds a date; AT TIME ZONE converts between zones.
Converting and handling NULL
CAST and CONVERT change types and fail on bad data. TRY_CAST and TRY_CONVERT return NULL instead, which is safer when importing text. COALESCE returns the first non-NULL value and is standard SQL; ISNULL is the SQL Server version for two values.
SELECT TRY_CONVERT(int, N'42') AS Ok, -- 42
TRY_CONVERT(int, N'4x2') AS Bad, -- NULL instead of an error
COALESCE(Phone, Mobile, N'-') AS Contact,
FORMAT(Price, 'N2', 'en-IN') AS Display -- slow; use for small results only
FROM Customers;Keep functions off indexed columns in WHERE
WHERE YEAR(OrderedAt) = 2026 must run YEAR on every row, so an index on OrderedAt cannot seek. Write the same filter as a range: WHERE OrderedAt >= '2026-01-01' AND OrderedAt < '2027-01-01'. The result is identical and the index is used.
The same applies to LEFT(Name, 3) = 'Pen' (use Name LIKE 'Pen%') and to wrapping a column in CONVERT. Put functions on the parameter side instead.
Interview question
Why is WHERE YEAR(OrderedAt) = 2026 slower than a date range?
Wrapping the column in a function makes the predicate non-sargable: SQL Server cannot seek an index on OrderedAt because it must compute YEAR for every row first, so it scans. Writing OrderedAt >= '2026-01-01' AND OrderedAt < '2027-01-01' compares the raw column with constants, so the optimizer can seek directly to the matching range.
Want the full details? Read the official Microsoft documentation for this topic.