Cleaning and splitting text

Change case, trim spaces, join columns together and pull pieces out of text.

Real data is messy: names typed in capitals, stray spaces, two facts crammed into one column. These functions clean text up and pull it apart.

Changing case

SELECT UPPER('Acme Manufacturing'),    -- 'ACME MANUFACTURING'
       LOWER('Acme Manufacturing');    -- 'acme manufacturing'

The main use is comparing text when you can’t trust the capitals. 'Acme' = 'ACME' is FALSE on most databases (lesson 9), so compare the lowered versions:

WHERE LOWER(name) = 'acme manufacturing'

Trimming spaces

A value typed as ' Acme ' won’t match 'Acme', and it’s hard to spot by eye because spaces are invisible.

SELECT TRIM('  Acme  '),      -- 'Acme'
       LTRIM('  Acme  '),     -- 'Acme  '  (left side only)
       RTRIM('  Acme  '),     -- '  Acme'  (right side only)
       LENGTH('  Acme  ');    -- 8: spaces count

LENGTH is a quick way to catch them: if a four-letter name has a length of 8, something’s hiding.

Joining text together

|| glues text together. Add your own spaces and punctuation as text in quotes:

SELECT name || ' (' || industry || ')' AS label
FROM accounts;                            -- 'Northwind Traders (Retail)'

The NULL trap: if any piece is NULL, the whole result is NULL.

SELECT 'Deal from ' || NULL;              -- NULL, not 'Deal from '

Wrap columns that might be empty in COALESCE (lesson 19): 'Source: ' || COALESCE(lead_source, 'Unknown'). Or use concat(), which skips NULLs:

SELECT concat('a', NULL, 'b'),             -- 'ab'
       concat_ws(' / ', 'a', NULL, 'c');   -- 'a / c': the first argument goes between each piece

Pulling out part of the text

Two functions do almost all the work:

Positions start at 1, not 0. Leave out the length and substr returns everything to the end.

Combine them to split on a character. Every deal name in the sample data is an account word, a space, then the product:

SELECT name,
       substr(name, 1, instr(name, ' ') - 1)  AS before_space,   -- 'Northwind'
       substr(name, instr(name, ' ') + 1)     AS after_space     -- 'Network Refresh'
FROM opportunities;

The - 1 stops before the space, and the + 1 starts just after it. Read it from the inside out: find the space, then cut there.

The same shape pulls the domain out of an email address, using '@' instead of a space. Matching contacts to accounts by email domain is a common CRM cleanup job.

Replacing text

SELECT REPLACE('555-867-5309', '-', '');   -- '5558675309'

REPLACE swaps every match, not just the first. Replace with '' to delete a character.

Common mistakes

Dialect notes

Two traps when moving between databases. In MySQL, || means OR, not join, so 'a' || 'b' returns 0. And SQL Server’s LEN ignores trailing spaces, so LEN('Acme ') is 4.

Snowflake notes

SPLIT_PART replaces most substr + instr combinations, and it returns an empty string, not an error, when the piece doesn’t exist. Text comparisons are case-sensitive, so LOWER or ILIKE are everyday tools there.

More in the field guide: String functions and Text: VARCHAR.

Try it

The product is everything after the first space in a deal’s name: Northwind Network Refresh is a Network Refresh deal. How many deals are there of each product? Return the product and the count, most deals first.

One row will look odd. That’s a test deal someone forgot to delete, and spotting rows like that is part of the job.

Write it in the sandbox below, then press Check answer.

Back to the top of the lesson