Day 13: String functions & pattern matching

A. Lesson overview

Activity breakdown

Business scenario. The customer import in Staging.CustomerImport is messy: names with extra spaces and lower-case letters, emails with capitals and leading spaces, phone numbers in different formats, countries written as “UK” or “united kingdom”, and two email addresses that cannot be valid. Before Day 15’s load, the data team needs a clean, standardised version and a report of what could not be fixed.

B. Theory explained clearly

Part 1 — Strings, lengths, Unicode and collation

char/nchar are fixed length and padded with spaces; varchar/nvarchar store only what you give them. RetailLab stores names as nvarchar (so Zoë, Søren and François are safe) and codes as varchar.

Collation decides how text is compared and sorted. RetailLab uses Latin1_General_100_CI_AS: case-insensitive ('A' = 'a'), accent-sensitive ('e' <> 'é'). Comparisons with = and LIKE follow it. To force a case-sensitive comparison in one expression: WHERE Email COLLATE Latin1_General_100_CS_AS = 'Alice…'.

Trailing spaces are ignored by = ('abc' = 'abc ' is TRUE) but leading spaces are not. LIKE does not ignore trailing spaces in the pattern.

Part 2 — Finding and extracting

DECLARE @e nvarchar(255) = N'alice.morgan@example.com';

SELECT  CHARINDEX(N'@', @e)                                     AS AtPos,      -- 13
        LEFT(@e, CHARINDEX(N'@', @e) - 1)                       AS LocalPart,  -- alice.morgan
        SUBSTRING(@e, CHARINDEX(N'@', @e) + 1, LEN(@e))         AS Domain;     -- example.com

Guard against “not found”. If there is no @, CHARINDEX returns 0 and LEFT(@e, -1) raises Invalid length parameter passed to the LEFT or SUBSTRING function. Protect with NULLIF(CHARINDEX(…), 0) (making the position NULL, so the result is NULL), or with a CASE. Passing a length longer than the string to SUBSTRING is harmless, which is why LEN(@e) is a convenient “rest of the string”.

Part 3 — Changing text

Collapsing repeated spaces. REPLACE(s, ' ', ' ') only halves runs of spaces. A reliable one-pass trick uses two marker characters that never occur in the data:

REPLACE(REPLACE(REPLACE(TRIM(s), N' ', N'<>'), N'><', N''), N'<>', N' ')
-- 'Daisy   Holt' → 'Daisy<><><>Holt' → 'Daisy<>Holt' → 'Daisy Holt'

Part 4 — Pattern matching

LIKE

To match a literal %, _ or [, either wrap it in brackets ('%[%]%') or use ESCAPE: LIKE '%50!%%' ESCAPE '!'.

LIKE is good for simple shapes. It cannot express repetition (“one or more letters”) or alternatives, so format validation with LIKE is approximate: '%_@_%._%' accepts a@b.c but also a@b..c.

Regular expressions (SQL Server 2025)

SQL Server 2025 adds regular-expression functions: REGEXP_LIKE, REGEXP_COUNT, REGEXP_INSTR, REGEXP_REPLACE, REGEXP_SUBSTR, and the table-valued REGEXP_MATCHES and REGEXP_SPLIT_TO_TABLE. REGEXP_LIKE, REGEXP_MATCHES and REGEXP_SPLIT_TO_TABLE require database compatibility level 170.

WHERE REGEXP_LIKE(Email, '^[a-z0-9._%+-]+@[a-z0-9.-]+\.[a-z]{2,}$', 'i')

REGEXP_LIKE returns a Boolean, so it is used as a predicate (WHERE, CASE WHEN, CHECK), not as a column value. Regular expressions are powerful but expensive on large tables, and no email pattern is perfect — validate the format, then confirm by sending an email. On older servers or other platforms, fall back to LIKE/PATINDEX patterns.

Part 5 — Splitting and aggregating

STRING_SPLIT

SELECT value, ordinal
FROM   STRING_SPLIT(N'tent,2-person,lightweight', N',', 1);   -- third argument 1 = return ordinal

• The separator must be a single character.
• Without ordinal, the output order is not guaranteed. Use the ordinal whenever position matters (“the first tag”).
• Empty pieces are returned as empty strings: 'a,,b' gives three rows.
• It is a table-valued function, so use it with CROSS APPLY to split a column (Day 10).

STRING_AGG

STRING_AGG(expression, separator) [WITHIN GROUP (ORDER BY …)]

• Concatenates the values of a group, ignoring NULLs.
• WITHIN GROUP (ORDER BY …) makes the order deterministic.
• There is no DISTINCT option: de-duplicate in a derived table or CTE first.
• If the inputs are varchar(n)/nvarchar(n), the result is limited to 8,000 bytes and exceeding it raises an error; cast the input to nvarchar(max) for long lists.

STRING_SPLIT and STRING_AGG are inverses in spirit: one turns a list into rows, the other rows into a list. Storing comma-separated lists in a column — as Inventory.Product.Tags does, for teaching — makes searching and validation harder; a separate ProductTag table is the normalised design.

C. Dataset and starting state

Preparation. None if RetailLab is unchanged. Otherwise run scripts/setup/04_reset_training_data.sql in SQLCMD Mode (destructive) or scripts 00 → 03.

Validation. Expected: 170, 15, 30.

USE RetailLab;
GO

SELECT  (SELECT compatibility_level FROM sys.databases WHERE name = N'RetailLab') AS CompatLevel,
        (SELECT COUNT(*) FROM Staging.CustomerImport)                            AS ImportRows,
        (SELECT COUNT(*) FROM Inventory.Product WHERE Tags IS NOT NULL)           AS TaggedProducts;

D. Worked examples

Worked example 1 — Lengths, bytes and Unicode

SELECT  LEN(N'Zoë')               AS LenUnicode,      -- 3
        DATALENGTH(N'Zoë')        AS BytesUnicode,    -- 6
        LEN('abc   ')             AS LenTrailing,     -- 3
        DATALENGTH('abc   ')      AS BytesTrailing,   -- 6
        CASE WHEN 'abc' = 'abc   ' THEN 'equal' ELSE 'different' END AS TrailingCompare,   -- equal
        CASE WHEN 'abc' = '   abc' THEN 'equal' ELSE 'different' END AS LeadingCompare;    -- different

Expected result: 3, 6, 3, 6, equal, different.

Worked example 2 — Customers by email domain

SELECT  COALESCE(SUBSTRING(Email, NULLIF(CHARINDEX(N'@', Email), 0) + 1, LEN(Email)), N'(none)') AS Domain,
        COUNT(*) AS Customers
FROM    Sales.Customer
GROUP BY COALESCE(SUBSTRING(Email, NULLIF(CHARINDEX(N'@', Email), 0) + 1, LEN(Email)), N'(none)')
ORDER BY Customers DESC, Domain;

Expected result: 11 rows. example.com 22, then (none) 4 and example.fr 4, then example.de and example.ie 2 each, then six domains with 1 (example.dk, example.es, example.it, example.net, example.org, example.se).

What to notice: NULLIF(CHARINDEX(…), 0) makes the start position NULL when there is no @, so SUBSTRING returns NULL instead of the whole string — and a NULL email also produces NULL. COALESCE then labels both. Repeating the expression in GROUP BY is clumsy; Task 1 tidies it with CROSS APPLY.

Worked example 3 — Product codes that follow the house format

SELECT  ProductCode,
        CASE WHEN ProductCode LIKE '[A-Z][A-Z][A-Z]-[0-9][0-9][0-9]' THEN 'Valid' ELSE 'Invalid' END AS CodeFormat
FROM   (VALUES ('TNT-001'), ('tnt-001'), ('TNT-01'), ('TN1-001'), ('TNT-0011')) AS v (ProductCode)
ORDER BY ProductCode;

Expected result: TN1-001 Invalid, TNT-001 Valid, tnt-001 Valid, TNT-01 Invalid, TNT-0011 Invalid. The lower-case code passes because [A-Z] follows the case-insensitive collation. To insist on capitals, add COLLATE Latin1_General_100_CS_AS after ProductCode — or use REGEXP_LIKE(ProductCode, '^[A-Z]{3}-[0-9]{3}$'), which is case-sensitive by default.

Run the same LIKE against Inventory.Product: all 30 codes are valid.

Worked example 4 — The first number in a product name (PATINDEX)

SELECT  ProductCode,
        ProductName,
        p.FirstDigitPos,
        LEFT(SUBSTRING(ProductName, p.FirstDigitPos, 20),
             PATINDEX('%[^0-9]%', SUBSTRING(ProductName, p.FirstDigitPos, 20) + 'x') - 1) AS LeadingNumber
FROM    Inventory.Product
CROSS APPLY (VALUES (PATINDEX('%[0-9]%', ProductName))) AS p (FirstDigitPos)
WHERE   p.FirstDigitPos > 0
ORDER BY ProductCode;

Expected result: 9 rows. For example: BPK-001 Daypack 25L → position 9, 25; COK-003 Gas Canister 230g → 14, 230; TNT-002 Summit 4-Person Tent → 8, 4.

How it works: from the first digit, take the following characters and find the first non-digit with PATINDEX('%[^0-9]%', …). Appending 'x' guarantees a non-digit exists even when the number ends the string. REGEXP_SUBSTR(ProductName, '[0-9]+') does the same in one call on SQL Server 2025.

Worked example 5 — Validating staging emails: LIKE vs REGEXP_LIKE

SELECT  ImportRowID,
        Email,
        CASE WHEN TRIM(Email) LIKE '%_@_%._%' THEN 'Pass' ELSE 'Fail' END                       AS LikeCheck,
        CASE WHEN REGEXP_LIKE(TRIM(Email), '^[a-z0-9._%+-]+@[a-z0-9.-]+\.[a-z]{2,}$', 'i')
             THEN 'Pass' ELSE 'Fail' END                                                        AS RegexCheck
FROM    Staging.CustomerImport
ORDER BY ImportRowID;

Expected result: 15 rows. Row 7 (sam.jones@) and row 15 (tariq.aziz@example) fail both checks; row 12 (NULL) shows Fail in both because a NULL comparison is not TRUE. Every other row passes. Try 'a@b..c' in a VALUES row: LIKE passes it, the regular expression does not.

Worked example 6 — Tags into rows, with position (STRING_SPLIT + ordinal)

SELECT  p.ProductCode, t.ordinal, t.value AS Tag
FROM    Inventory.Product AS p
CROSS APPLY STRING_SPLIT(p.Tags, N',', 1) AS t
WHERE   p.ProductCode IN ('TNT-001', 'NAV-004')
ORDER BY p.ProductCode, t.ordinal;

Expected result: 6 rows — NAV-004: 1 satellite, 2 electronics, 3 safety; TNT-001: 1 tent, 2 2-person, 3 lightweight.

Worked example 7 — A list per group (STRING_AGG)

SELECT  p.CategoryID,
        COUNT(*)                                                                     AS Products,
        STRING_AGG(p.ProductCode, N', ') WITHIN GROUP (ORDER BY p.ProductCode)       AS ProductCodes
FROM    Inventory.Product AS p
GROUP BY p.CategoryID
ORDER BY p.CategoryID;

Expected result: 12 rows (categories that have products). Category 2: TNT-001, TNT-002, TNT-003, TNT-004; category 14: JKT-001, JKT-002.

E. Guided practical lab — Standardise the customer import

Produce one cleaned row per import row with: CleanFullName, FirstName, LastName, CleanEmail, EmailStatus, CleanPhone (international format) and CleanCountry, then summarise what needs attention.

Rules agreed with the data team

Step 1 — Clean the name in one place

1. Do: Use CROSS APPLY (VALUES …) to compute the cleaned name once.
2. Run:

   USE RetailLab;
   GO
   
   SELECT  i.ImportRowID, i.FullName, n.CleanFullName
   FROM    Staging.CustomerImport AS i
   CROSS APPLY (VALUES (NULLIF(REPLACE(REPLACE(REPLACE(TRIM(i.FullName), N' ', N'<>'), N'><', N''), N'<>', N' '), N'')))
               AS n (CleanFullName)
   ORDER BY i.ImportRowID;

3. Observe: row 2 ' ben carter ' → ben carter; row 5 'Daisy Holt' → Daisy Holt; rows 8 (NULL) and 9 ('') → NULL.
4. Why it matters: NULLIF(…, N'') turns a blank name into NULL, so “missing” has one representation.
5. Confirm: exactly 2 NULL clean names.

Step 2 — Split and capitalise

1. Do: Add a second APPLY that finds the first space, then build FirstName and LastName.
2. Run:

   SELECT  i.ImportRowID, n.CleanFullName, s.FirstName, s.LastName
   FROM    Staging.CustomerImport AS i
   CROSS APPLY (VALUES (NULLIF(REPLACE(REPLACE(REPLACE(TRIM(i.FullName), N' ', N'<>'), N'><', N''), N'<>', N' '), N'')))
               AS n (CleanFullName)
   CROSS APPLY (VALUES (CHARINDEX(N' ', n.CleanFullName))) AS sp (SpacePos)
   CROSS APPLY (VALUES (
       UPPER(LEFT(LEFT(n.CleanFullName, NULLIF(sp.SpacePos, 0) - 1), 1))
         + LOWER(SUBSTRING(LEFT(n.CleanFullName, NULLIF(sp.SpacePos, 0) - 1), 2, 100)),
       UPPER(LEFT(SUBSTRING(n.CleanFullName, NULLIF(sp.SpacePos, 0) + 1, 100), 1))
         + LOWER(SUBSTRING(SUBSTRING(n.CleanFullName, NULLIF(sp.SpacePos, 0) + 1, 100), 2, 100))
   )) AS s (FirstName, LastName)
   ORDER BY i.ImportRowID;

3. Observe: row 2 → Ben, Carter; row 10 → Ines, Moreau; rows 8 and 9 → NULL, NULL.
4. Why it matters: NULLIF(sp.SpacePos, 0) protects LEFT from a negative length if a name had no space. Simple capitalisation is wrong for names like McDonald or van der Berg — real systems keep the customer’s own spelling where possible.
5. Confirm: no row raises Invalid length parameter.

Step 3 — Email, phone and country

1. Do: Add the remaining cleaned columns.
2. Run: add to the SELECT list of step 2 (and keep its APPLYs):

           , e.CleanEmail
           , CASE WHEN e.CleanEmail IS NULL THEN 'Missing'
                  WHEN REGEXP_LIKE(e.CleanEmail, '^[a-z0-9._%+-]+@[a-z0-9.-]+\.[a-z]{2,}$') THEN 'Valid'
                  ELSE 'Invalid' END                                              AS EmailStatus
           , CASE WHEN ph.Compact LIKE '07%' THEN N'+44' + SUBSTRING(ph.Compact, 2, 30)
                  ELSE ph.Compact END                                             AS CleanPhone
           , CASE WHEN UPPER(TRIM(i.Country)) IN (N'UK', N'UNITED KINGDOM') THEN N'United Kingdom'
                  ELSE TRIM(i.Country) END                                        AS CleanCountry

   and add these APPLYs before ORDER BY:

   CROSS APPLY (VALUES (LOWER(TRIM(i.Email))))        AS e  (CleanEmail)
   CROSS APPLY (VALUES (REPLACE(i.Phone, ' ', '')))   AS ph (Compact)

3. Observe: row 3 phone 07700 900301 → +447700900301; row 13 country united kingdom → United Kingdom; row 2 UK → United Kingdom; row 15 email tariq.aziz@example → Invalid.
4. Why it matters: Each rule is visible and testable in one place. Because the email has been lower-cased, the regular expression needs no 'i' flag.
5. Confirm: EmailStatus counts: 12 Valid, 2 Invalid, 1 Missing.

Step 4 — Summarise the issues

1. Do: Wrap the step-3 query in a CTE Cleaned (remove its ORDER BY) and summarise.
2. Run:

   SELECT  COUNT(*)                                                     AS ImportRows,
           SUM(CASE WHEN CleanFullName IS NULL THEN 1 ELSE 0 END)       AS MissingName,
           SUM(CASE WHEN EmailStatus = 'Invalid' THEN 1 ELSE 0 END)     AS InvalidEmail,
           SUM(CASE WHEN EmailStatus = 'Missing' THEN 1 ELSE 0 END)     AS MissingEmail,
           SUM(CASE WHEN CleanPhone IS NULL THEN 1 ELSE 0 END)          AS MissingPhone,
           COUNT(DISTINCT CleanEmail)                                   AS DistinctEmails
   FROM    Cleaned;

3. Observe: 15, 2, 2, 1, 4, 11.
4. Why it matters: After cleaning, row 5 (' Daisy.Holt@Example.com') is recognisably the same person as rows 3 and 4. 11 distinct emails among 14 non-NULL values means 3 duplicate rows remain — Day 15 removes them during the load.
5. Confirm: the six numbers match.

F. Independent practice

Task 1 — Email domains, tidied

Rewrite worked example 2 so that the domain expression is written once, using CROSS APPLY (VALUES …). Same output.

Success criteria: 11 rows, example.com 22 first.

Task 2 — Numbers in product names

Return products whose name contains at least one digit: ProductCode, ProductName and LeadingNumber as an int. Sort by LeadingNumber descending, then code.

Success criteria: 9 rows: LGT-001 Headtorch 400 Lumen (400) first, then COK-003 (230), BPK-002 (65), BPK-001 (25), BPK-003 (12), TNT-002 (4), BSL-003 and SLP-001 (3), and TNT-001 (2).

Task 3 — What did each customer buy in December? (STRING_AGG)

For December 2025 (non-cancelled), return one row per customer with Products (number of distinct products) and ProductCodes — a comma-separated list of distinct product codes in alphabetical order.

Success criteria: 20 rows. Customer 1: 10 products, BSL-001, COK-003, FTW-001, JKT-003, JKT-005, LGT-001, NAV-001, SLP-003, TNT-001, TNT-003. Customer 13: BPK-002, NAV-002.

Task 4 — Products by first tag (ordinal)

Using STRING_SPLIT(…, ',', 1), count products by their first tag. Return FirstTag and Products, most common first, then alphabetically.

Success criteria: 19 rows; jacket 4, tent 4, backpack 3, then base layer, boots and sleeping bag with 2 each, then 13 tags with 1.

Stretch challenge — Masked contact details

For a customer-services screen, show each customer’s email masked as first character + *** + last character of the local part + @domain, e.g. a***n@example.com, and the phone with all but the last four digits replaced by * (spaces removed). Return CustomerID, MaskedEmail, MaskedPhone for customers 1–5.

Success criteria: customer 1: a***n@example.com, *********0101; customer 3: c***s@example.net, NULL phone; customer 5: NULL email, *********0105.

G. Solutions and explanations

SELECT  d.Domain, COUNT(*) AS Customers
FROM    Sales.Customer AS c
CROSS APPLY (VALUES (COALESCE(SUBSTRING(c.Email, NULLIF(CHARINDEX(N'@', c.Email), 0) + 1, LEN(c.Email)),
                              N'(none)'))) AS d (Domain)
GROUP BY d.Domain
ORDER BY Customers DESC, d.Domain;

SELECT  ProductCode,
        ProductName,
        TRY_CAST(LEFT(SUBSTRING(ProductName, p.Pos, 20),
                      PATINDEX('%[^0-9]%', SUBSTRING(ProductName, p.Pos, 20) + 'x') - 1) AS int) AS LeadingNumber
FROM    Inventory.Product
CROSS APPLY (VALUES (PATINDEX('%[0-9]%', ProductName))) AS p (Pos)
WHERE   p.Pos > 0
ORDER BY LeadingNumber DESC, ProductCode;

WITH DecProducts AS
(
    SELECT DISTINCT o.CustomerID, p.ProductCode
    FROM   Sales.SalesOrder     AS o
    JOIN   Sales.SalesOrderLine AS l ON l.OrderID   = o.OrderID
    JOIN   Inventory.Product    AS p ON p.ProductID = l.ProductID
    WHERE  o.OrderStatus <> 'Cancelled'
      AND  o.OrderDate >= '2025-12-01' AND o.OrderDate < '2026-01-01'
)
SELECT  CustomerID,
        COUNT(*)                                                               AS Products,
        STRING_AGG(ProductCode, ', ') WITHIN GROUP (ORDER BY ProductCode)      AS ProductCodes
FROM    DecProducts
GROUP BY CustomerID
ORDER BY CustomerID;

SELECT  t.value  AS FirstTag,
        COUNT(*) AS Products
FROM    Inventory.Product AS p
CROSS APPLY STRING_SPLIT(p.Tags, N',', 1) AS t
WHERE   t.ordinal = 1
GROUP BY t.value
ORDER BY Products DESC, FirstTag;

SELECT  c.CustomerID,
        CASE WHEN e.AtPos > 0
             THEN STUFF(LEFT(c.Email, e.AtPos - 1), 2, e.AtPos - 3, N'***') + SUBSTRING(c.Email, e.AtPos, LEN(c.Email))
        END                                                            AS MaskedEmail,
        CASE WHEN ph.Compact IS NOT NULL
             THEN REPLICATE('*', LEN(ph.Compact) - 4) + RIGHT(ph.Compact, 4)
        END                                                            AS MaskedPhone
FROM    Sales.Customer AS c
CROSS APPLY (VALUES (CHARINDEX(N'@', c.Email)))     AS e  (AtPos)
CROSS APPLY (VALUES (REPLACE(c.Phone, ' ', '')))    AS ph (Compact)
WHERE   c.CustomerID BETWEEN 1 AND 5
ORDER BY c.CustomerID;

H. Knowledge check

1. Understanding. What do LEN(N'Søren ') and DATALENGTH(N'Søren ') return?

2. Debugging. On a supplier table where some email values have no @, SELECT LEFT(Email, CHARINDEX('@', Email) - 1) FROM … fails with Invalid length parameter. Why, and how do you fix it?

3. Query interpretation. Which of these match LIKE 'J[A-K]%-00[^1]' in RetailLab: JKT-002, JKT-001, jkt-005, JLT-002?

4. Understanding. Why should you pass 1 as the third argument of STRING_SPLIT when you need “the first tag”?

5. Debugging. STRING_AGG(ProductCode, ', ') returns codes in a different order each time and repeats some codes. Fix both problems.

I. Troubleshooting and cleanup

Cleanup. Day 13 is read-only; nothing to clean up. Keep RetailLab (and the Day 12 objects) for Day 14.

J. Completion checklist, recap and references

Completion checklist

• Worked example 1 returned 3, 6, 3, 6, equal, different.
• Worked example 5 failed rows 7, 12 and 15 with both checks.
• Your lab summary shows 15, 2, 2, 1, 4, 11.
• Tasks 1–4 match the success criteria (11, 9, 20 and 19 rows).
• You used STRING_SPLIT with the ordinal argument when position mattered.

Recap

• LEN counts characters without trailing spaces; DATALENGTH counts bytes. Use nvarchar/N'…' for Unicode.
• Collation controls case and accent sensitivity for = and LIKE; override with COLLATE.
• CHARINDEX/PATINDEX return 0 when not found — guard before using the position in LEFT/SUBSTRING.
• TRIM, REPLACE, TRANSLATE, STUFF clean text; collapse repeated spaces with the marker trick.
• LIKE handles simple shapes; REGEXP_LIKE (SQL Server 2025, compatibility 170) handles real patterns and is case-sensitive by default.
• STRING_SPLIT turns lists into rows (use the ordinal for position); STRING_AGG … WITHIN GROUP turns rows into ordered lists (de-duplicate first).

Next: Day 14 — Week 2 review: a complex report combining joins, set operations, APPLY, PIVOT, dates and strings.

References (Microsoft Learn)

• String functions (Transact-SQL)
• LIKE (Transact-SQL)
• PATINDEX (Transact-SQL)
• REGEXP_LIKE (Transact-SQL)
• Regular expressions functions (Transact-SQL)
• STRING_SPLIT (Transact-SQL)
• STRING_AGG (Transact-SQL)
• TRANSLATE (Transact-SQL)
• Collation and Unicode support
• Regex support in SQL Server 2025 — general availability (Azure SQL Dev Corner)