SQL for .NET developers Beginner

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.

Which function for which job
  1. Clean textTRIM, UPPER, REPLACE, SUBSTRING
  2. Join textCONCAT, CONCAT_WS, STRING_AGG
  3. DatesDATEADD, DATEDIFF, EOMONTH, DATEFROMPARTS
  4. Convert safelyTRY_CONVERT and TRY_CAST return NULL on bad data
  5. Missing valuesCOALESCE or ISNULL
  6. 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

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

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

Your progress is saved only in this browser.

Want the full details? Read the official Microsoft documentation for this topic.