IntermediateSQL · 25 min

String Manipulation: Intermediate

Past basic extraction, real string manipulation involves fuzzy matching for entity resolution, unicode normalization for international data, parsing structured-but-not-quite-JSON formats (pipe-delimited logs, key=value strings), and the export-side problem of formatting numbers and dates as strings consistently. The patterns are less mechanical than the beginner toolkit; they require thinking about what 'equal' means when the data is dirty.

Apply fuzzy-matching functions (Levenshtein, Jaro-Winkler, SOUNDEX) for entity resolution

Handle unicode normalization (NFC vs NFD) for international names and addresses

Parse structured-but-not-JSON formats (pipe-delimited, key=value, ndjson)

Format numbers and dates as strings for export with consistent precision and locale

REPLACE, TRIM, and Case-Insensitive Matching

Daily Life

Interviews

Recognize string manipulation needs: log parsing, URL extraction, name splitting, code parsing.

The question that recurs in interviews involving customer data: 'find pairs of customer records that are likely the same person but have small differences in name or address.' This is fuzzy matching; the answer involves similarity scoring. The candidate who reaches for Levenshtein or Jaro-Winkler (depending on the data) and a threshold tuning conversation is the candidate who has built entity-resolution pipelines. The candidate who tries to match on LOWER equality is the one who hasn't yet seen the data.

Three patterns this lesson covers

First: fuzzy matching. Levenshtein distance counts character edits; Jaro-Winkler scores similarity giving extra weight to prefix matches; SOUNDEX matches phonetically. Each fits a different kind of similarity. Second: unicode normalization. The same character can have multiple binary encodings (NFC composed, NFD decomposed); strings that look identical may not match without normalization. Third: parsing structured-but-not-JSON. Production logs and exports use formats like 'key=value;key2=value2' that need REGEXP plus splitting to extract. The patterns compose with the basic extraction tools.

You are being tested on intermediate strings when you hear:

▸"find pairs of customer records that are likely the same person"
▸"the names in this dataset are in multiple scripts and encodings"
▸"parse this pipe-delimited log line into structured fields"
▸"format numbers with thousands separators for the export"
▸"the join doesn't match because 'San José' and 'San Jose' have different unicode"

LIKE and Wildcard Pattern Matching

Daily Life

Interviews

Extract substrings by position, find delimiters with STRPOS/INSTR, and split strings into parts.

Fuzzy matching scores similarity between two strings. Identical strings have a perfect score; strings with small differences have high scores; very different strings have low scores. The choice of function depends on the kind of similarity that matters: edit distance for typos, phonetic similarity for misspellings, prefix-weighted for names.

Levenshtein distance


	SELECT
	LEVENSHTEIN('Mariam', 'Miriam') AS distance ;


	SELECT
	EDITDISTANCE('Mariam', 'Miriam') AS distance ;


	SELECT
	EDIT_DISTANCE('Mariam', 'Miriam') AS distance ;

Levenshtein counts character-level edits (insertions, deletions, substitutions). 'Mariam' to 'Miriam' is 2 edits. The distance is the raw score; for similarity, divide by the longer string's length to get a normalized score (0 = identical, 1 = completely different). Use Levenshtein when typo similarity is what you want: catching 'Smith' vs 'Smtih' as similar.

Jaro-Winkler similarity

Jaro-Winkler weights early-character matches more heavily than later-character matches. 'Marie' and 'Maria' score high because the first four characters match. 'Marie' and 'Carolyn' score low. The function is designed for name matching where prefix matches matter (people often have consistent first letters of their names). Use Jaro-Winkler for name similarity; use Levenshtein for general typo detection.

SOUNDEX and metaphone

SOUNDEX maps a string to a phonetic code; two strings with the same SOUNDEX sound alike. 'Smith' and 'Smyth' have the same SOUNDEX. The function is English-centric and somewhat coarse; metaphone is a refinement that handles more cases. Use SOUNDEX for phonetic matching of English names. State this when designing: 'SOUNDEX for phonetic; Levenshtein for typo; Jaro-Winkler for prefix-aware name matching. Each fits a different similarity model.'

•Levenshtein wins when

General typo detection (any character edit)
Symmetric similarity (no positional bias)
Strings of similar length
Default choice for non-name matching

✓Jaro-Winkler wins when

Name matching where prefix consistency matters
Strings of varying length
Need a normalized 0-1 similarity score directly
Default choice for person-name matching

The threshold question

Every fuzzy match needs a threshold. 'How similar is similar enough?' A Levenshtein distance of 2 is acceptable for short names; a distance of 10 is acceptable for long company names. Jaro-Winkler thresholds typically fall between 0.85 and 0.95 for entity matching. The right threshold depends on the data; tune against a labeled set of known matches and non-matches. State this when designing: 'the threshold is tuned against a hand-labeled sample of 500-1000 pairs; the tuning is the calibration step.'

Splitting a Delimited Column into Rows

Daily Life

Interviews

Clean strings with REPLACE, TRIM whitespace, normalize with LOWER/UPPER, and handle encoding edge cases.

Two strings can look identical, print identically, and still not match in SQL because they have different unicode byte representations. The character é can be a single codepoint (NFC, composed form) or two codepoints (NFD, decomposed: 'e' + combining acute accent). The strings print the same; the bytes differ; equality fails. International data (names, addresses) hits this constantly.

NFC vs NFD

NFC (Canonical Composition) merges decomposable characters into precomposed forms. NFD (Canonical Decomposition) splits them into base + combining marks. NFC is the convention for most web data; NFD shows up in some macOS contexts and CSV exports. The normalization functions in most engines convert between forms; running every input through NFC normalization at the source is the discipline. State this when designing: 'every text column gets NFC normalization at the source CTE; downstream comparisons operate on the canonical form.'

Engine-specific normalization


	SELECT
	NORMALIZE(name, NFC) AS name_nfc
	FROM users ;



	SELECT
	NORMALIZE(name, NFC) AS name_nfc
	FROM users ;

Postgres and BigQuery support NORMALIZE natively. Snowflake doesn't; the workaround is to normalize at the ingestion layer (in Python or another preprocessing step) before loading. State this when designing: 'NORMALIZE in the warehouse where supported; ingestion-layer normalization otherwise.'

Case insensitivity across scripts

LOWER works for ASCII; for Latin-1 it usually works; for non-Latin scripts (Greek, Cyrillic, Turkish) the behavior is locale-dependent. Turkish has a famous case: the lowercase of 'I' is 'ı' (dotless), not 'i'. Most engines have a default Unicode-aware LOWER; some have locale-specific variants. For most analytics work, LOWER is sufficient; for locale-critical workloads, explicit locale specification is needed.

At Wise in 2024 (the cross-border payments company), the customer-master pipeline added a unicode normalization step after a six-week investigation into why 'José María' from Spanish forms wasn't matching 'José María' from German forms. The two strings were visually identical but byte-different (NFC vs NFD). The normalization step touched every text field at the source and added roughly 1% to ingestion cost; the entity-resolution match rate improved by 4% globally, with the largest gains on Spanish and Vietnamese customer records. The runbook line is 'every text field flows through unicode normalization at the source; downstream comparisons are byte-exact only after normalization.'

Removing accents for matching

Sometimes you want 'José' to match 'Jose' for analytics purposes. The pattern is to first NFD-decompose (split base from accent), then strip the combining marks, then re-NFC if needed. Most engines have an 'unaccent' extension or function for this. State this when designing: 'accent removal is a separate normalization beyond NFC; use unaccent or equivalent when the consumer wants accent-insensitive matching.'

Normalizing Inconsistent Text Values

Daily Life

Interviews

Write LIKE patterns with wildcards, use REGEXP_MATCH/REGEXP_EXTRACT for complex patterns, and know dialect differences.

Production logs and exports use formats that are structured but not JSON: pipe-delimited columns, key=value pairs, NDJSON (newline-delimited JSON). Parsing them is a composition of SPLIT, REGEXP_EXTRACT, and the JSON tools from the semi-structured lesson.

Pipe-delimited logs

	/* A log line like: '2024-03-15\|user_id=123\|event_type=login\|amount=99.50' */
	WITH parsed AS (
	SELECT
	SPLIT_PART(log_line, '\|', 1) AS timestamp_str,
	REGEXP_EXTRACT(
	log_line,
	'user_id=([^\|]+)'
	) AS user_id,
	REGEXP_EXTRACT(
	log_line,
	'event_type=([^\|]+)'
	) AS event_type,
	CAST(
	REGEXP_EXTRACT(
	log_line,
	'amount=([^\|]+)'
	)
	AS NUMERIC
	) AS amount
	FROM raw_logs
	)

	SELECT
	*
	FROM parsed

Reading the pattern

SPLIT_PART for the timestamp because it's the first piece. REGEXP_EXTRACT for the key=value pairs because each one is a structured but variable-position match. Cast the amount to NUMERIC because the extraction returns text. The pattern composes the basic tools into a parser; the result is structured columns ready for downstream queries.

NDJSON files

NDJSON is one JSON object per line. The 'parsing' is just splitting on newlines and then applying the JSON extraction tools from the semi-structured lesson. Most engines have direct loaders for NDJSON; when you need to parse it in SQL (because it's stored in a TEXT column), the pattern is the same: split into lines, parse each line as JSON. State this when designing: 'NDJSON via the engine's native loader when possible; SQL-level parsing as the fallback when the data is in a text column.'

Querystring parsing

	/* Parse a URL querystring: 'utm_source=google&utm_medium=cpc&utm_campaign=spring2025' */
	WITH params AS (
	SELECT
	REGEXP_EXTRACT(
	querystring,
	'utm_source=([^&]+)'
	) AS source,
	REGEXP_EXTRACT(
	querystring,
	'utm_medium=([^&]+)'
	) AS medium,
	REGEXP_EXTRACT(
	querystring,
	'utm_campaign=([^&]+)'
	) AS campaign
	FROM events
	)

	SELECT
	*
	FROM params

Querystring is a canonical example of the key=value-with-separator format. Each param is extracted with a separate REGEXP_EXTRACT. URL-decoding the values (in case of special characters) may need a separate function (most engines have URL_DECODE). State this when designing: 'querystring parsing is per-key REGEXP_EXTRACT; URL-decode if values can contain encoded characters.'

Handling Variable-Length Fields

Daily Life

Interviews

Discuss with the interviewer when string parsing belongs in SQL vs Python preprocessing, and the maintainability tradeoffs.

The last common intermediate pattern: formatting values as strings for export. Numbers with thousands separators, dates in specific formats, currency with locale-appropriate symbols. The TO_CHAR (or FORMAT) function is the workhorse; the locale parameters are the tuning knobs.

Formatting numbers

	-- Format a number with thousands separators and 2 decimal places
	-- Postgres
	SELECT TO_CHAR(amount, 'FM999,999,990.00') FROM orders;

	-- Snowflake
	SELECT TO_CHAR(amount, '999,999.99') FROM orders;

	-- BigQuery
	SELECT FORMAT('%\'.2f', amount) FROM orders;

Formatting dates


	SELECT
	TO_CHAR(order_date, 'YYYY-MM-DD HH24:MI:SS')
	FROM orders ;


	SELECT
	FORMAT_DATETIME('%Y-%m-%d %H:%M:%S', order_date)
	FROM orders ;

Date formatting is engine-specific in syntax but consistent in capability. ISO 8601 format (YYYY-MM-DD HH:MI:SS) is the default for exports. Locale-specific formats (DD/MM/YYYY for European, MM/DD/YYYY for US) need explicit format strings. State this when designing: 'ISO 8601 for machine-readable exports; locale-specific only for human-facing dashboards where the consumer's locale is known.'

Currency and locale

Currency formatting needs both the number format and a currency symbol. Most engines support locale-aware formatting via TO_CHAR with a locale parameter, but the syntax varies. For multi-currency reporting, store amounts as numeric and format at the BI layer where the consumer's locale is known; SQL-level currency formatting is rarely the right place for multi-locale displays.

The closing thought

Intermediate string work is fuzzy matching when equality isn't precise, unicode normalization when the bytes don't match the eye, parsing structured-but-not-JSON formats, and formatting for export. Each pattern fits a specific real-world need; the toolkit composes with the beginner extraction patterns. The candidate who knows when to reach for Levenshtein vs SOUNDEX vs LOWER equality is the candidate who has shipped against dirty data; the candidate who tries to make everything fit LOWER equality is the one who hasn't.

❯❯❯PUTTING IT ALL TOGETHER

> You are in a data engineering interview at a customer-data company. The interviewer asks: 'Find pairs of customer records that are likely the same person but have small differences in name or address.'

You name the tools: 'Fuzzy matching. Jaro-Winkler for names because prefix matches matter; the threshold is tuned against a labeled sample. Unicode normalization first because international data has invisible byte differences.'

You also reach for blocking: 'Compare only pairs sharing some blocking key (same zip, same dob_year). Within blocks, score with Jaro-Winkler. The block reduces the comparison space from N² to N × block_size.'

Follow-up: 'The same name in different scripts isn't matching.' You say: 'Unicode normalization. NFC vs NFD; the same character can have two byte representations. Normalize at the source CTE; downstream comparisons are byte-exact only on the canonical form.'

Follow-up: 'How do we handle accents?' You say: 'Separate normalization. NFD-decompose, strip combining marks, re-NFC. Use the engine's unaccent function. Accent-insensitive matching is a different policy than NFC; pick based on the consumer's intent.'

Closing: 'Fuzzy matching plus normalization plus blocking. The patterns compose; each fits a real-world failure mode.'

KEY TAKEAWAYS

Pick the similarity function from the kind of error you expect: Levenshtein counts character edits for typos, Jaro-Winkler weights prefix matches for names, SOUNDEX matches phonetically for English spellings such as Smith and Smyth.

Levenshtein returns a raw edit count, so divide by the longer string's length to get a comparable score, and tune the cutoff against a hand-labeled sample; Jaro-Winkler thresholds for entity matching usually land between 0.85 and 0.95.

Two visually identical strings can fail equality because one is NFC composed and the other NFD decomposed, so run every text column through NORMALIZE(name, NFC) at the source CTE, or normalize at ingestion on engines like Snowflake that have no native function.

Accent-insensitive matching is a separate step beyond NFC: decompose, strip the combining marks, then recompose, or use the engine's unaccent equivalent.

Structured-but-not-JSON formats parse as a composition: SPLIT_PART for fixed positions, REGEXP_EXTRACT per key for key=value and querystring pairs, then an explicit cast because extraction always returns text.

Export formatting uses TO_CHAR or FORMAT with ISO 8601 for machine-readable output; leave multi-currency and locale-specific display to the BI layer where the consumer's locale is known.

Parsing messy strings in SQL is ugly but interviewers love testing it

Category: SQL
Difficulty: intermediate
Duration: 25 minutes
Challenges: 0 hands-on challenges

Topics covered: REPLACE, TRIM, and Case-Insensitive Matching, LIKE and Wildcard Pattern Matching, Splitting a Delimited Column into Rows, Normalizing Inconsistent Text Values, Handling Variable-Length Fields

Lesson Sections

REPLACE, TRIM, and Case-Insensitive Matching (concepts: sqlRegexMatch)
The question that recurs in interviews involving customer data: 'find pairs of customer records that are likely the same person but have small differences in name or address.' This is fuzzy matching; the answer involves similarity scoring. The candidate who reaches for Levenshtein or Jaro-Winkler (depending on the data) and a threshold tuning conversation is the candidate who has built entity-resolution pipelines. The candidate who tries to match on LOWER equality is the one who hasn't yet seen
LIKE and Wildcard Pattern Matching (concepts: sqlSubstring)
Fuzzy matching scores similarity between two strings. Identical strings have a perfect score; strings with small differences have high scores; very different strings have low scores. The choice of function depends on the kind of similarity that matters: edit distance for typos, phonetic similarity for misspellings, prefix-weighted for names. Levenshtein distance Levenshtein counts character-level edits (insertions, deletions, substitutions). 'Mariam' to 'Miriam' is 2 edits. The distance is the r
Splitting a Delimited Column into Rows (concepts: sqlLowerUpper)
Two strings can look identical, print identically, and still not match in SQL because they have different unicode byte representations. The character é can be a single codepoint (NFC, composed form) or two codepoints (NFD, decomposed: 'e' + combining acute accent). The strings print the same; the bytes differ; equality fails. International data (names, addresses) hits this constantly. NFC vs NFD NFC (Canonical Composition) merges decomposable characters into precomposed forms. NFD (Canonical Dec
Normalizing Inconsistent Text Values (concepts: sqlRegexMatch)
Production logs and exports use formats that are structured but not JSON: pipe-delimited columns, key=value pairs, NDJSON (newline-delimited JSON). Parsing them is a composition of SPLIT, REGEXP_EXTRACT, and the JSON tools from the semi-structured lesson. Pipe-delimited logs Reading the pattern SPLIT_PART for the timestamp because it's the first piece. REGEXP_EXTRACT for the key=value pairs because each one is a structured but variable-position match. Cast the amount to NUMERIC because the extra
Handling Variable-Length Fields (concepts: sqlStringBuilding)
The last common intermediate pattern: formatting values as strings for export. Numbers with thousands separators, dates in specific formats, currency with locale-appropriate symbols. The TO_CHAR (or FORMAT) function is the workhorse; the locale parameters are the tuning knobs. Formatting numbers Formatting dates Date formatting is engine-specific in syntax but consistent in capability. ISO 8601 format (YYYY-MM-DD HH:MI:SS) is the default for exports. Locale-specific formats (DD/MM/YYYY for Europ