String Functions

Cleaning and Transforming Text

We’ll be honest: most of your time as a data analyst isn’t writing elegant analytical queries. It’s cleaning up someone else’s mess. Names with trailing spaces. Addresses stored as “city, state zip” in a single field. Phone numbers in twelve different formats.

String functions are your cleanup toolkit. Master these, and you’ll spend less time fighting data and more time analyzing it.

Dialect Comparison: String Concatenation

Concatenation syntax:

• PostgreSQL — || or CONCAT()
• MySQL — CONCAT()
• MSSQL — + or CONCAT()
• Oracle — || or CONCAT()

Behavior with NULL:

• PostgreSQL — || returns NULL; CONCAT() ignores NULL
• MySQL — CONCAT('a', NULL) returns NULL; use CONCAT_WS() to skip NULLs
• MSSQL — 'a' + NULL returns NULL; CONCAT() ignores NULL
• Oracle — || and CONCAT() both treat NULL as empty string, returning the non-NULL value

Safe Concatenation with COALESCE

In production, always assume data can be NULL. Here’s the defensive pattern:

-- Handle potential NULLs (no awkward double spaces)
SELECT
  COALESCE(first_name, '') || ' ' || COALESCE(last_name, '') AS full_name
FROM users;

-- Even better: TRIM the result to handle missing first OR last name
SELECT
  TRIM(
    COALESCE(first_name, '') || ' ' || COALESCE(last_name, '')
  ) AS full_name
FROM users;

Flags per Video

Use CONCAT with COALESCE to safely combine user_firstname and user_lastname, handling NULL values.

SUBSTRING: Extracting Parts of Strings

Extract a portion of a string by position:

Dialect Comparison: Substring

Substring:

• PostgreSQL — SUBSTRING(s, start, len)
• MySQL — SUBSTRING(s, start, len)
• MSSQL — SUBSTRING(s, start, len)
• Oracle — SUBSTR(s, start, len)

Left N chars:

• PostgreSQL — LEFT(s, n)
• MySQL — LEFT(s, n)
• MSSQL — LEFT(s, n)
• Oracle — SUBSTR(s, 1, n)

Right N chars:

• PostgreSQL — RIGHT(s, n)
• MySQL — RIGHT(s, n)
• MSSQL — RIGHT(s, n)
• Oracle — SUBSTR(s, -n)

Case Conversion: UPPER and LOWER

Case inconsistency is a common cause of “why doesn’t my JOIN work?” bugs. Standardize before comparing:

-- Convert to lowercase for case-insensitive comparison
SELECT *
FROM products
WHERE LOWER(category) = 'electronics';

-- Format for display
SELECT
  UPPER(first_name) || ' ' || UPPER(last_name) AS name_caps
FROM employees;

Good news: UPPER(s) and LOWER(s) work identically across all major dialects.

TRIM: Removing Whitespace

Invisible trailing spaces are insidious. ‘Admin’ and ‘Admin ’ look the same but won’t match. TRIM is your friend:

-- Remove leading/trailing spaces
SELECT TRIM('  hello world  ');
-- 'hello world'

-- Clean messy data before comparing
SELECT
  TRIM(product_name) AS clean_name
FROM products
WHERE TRIM(product_name) = 'Widget';

Variants: LTRIM and RTRIM

-- Left trim only
SELECT LTRIM('  hello');
-- 'hello'

-- Right trim only
SELECT RTRIM('hello  ');
-- 'hello'

Percentage of Shipable Orders

Use TRIM to clean address data before joining, then calculate the percentage.

LENGTH: String Length

Count characters in a string:

SELECT LENGTH('Hello');
-- 5

-- Find long product names (maybe truncated in reports)
SELECT
  product_name,
  LENGTH(product_name) AS name_length
FROM products
WHERE LENGTH(product_name) > 50;

Dialect Note

String length:

• PostgreSQL — LENGTH(s)
• MySQL — LENGTH(s)
• MSSQL — LEN(s)
• Oracle — LENGTH(s)

Finding Substrings: POSITION

Find where a substring appears:

-- POSITION(substring IN string)
SELECT POSITION('@' IN 'user@email.com');
-- 5

-- Find emails with specific domain
SELECT email
FROM users
WHERE POSITION('@gmail.com' IN email) > 0;

Dialect Comparison: Position/Index

Find position:

• PostgreSQL — POSITION(sub IN s)
• MySQL — LOCATE(sub, s)
• MSSQL — CHARINDEX(sub, s)
• Oracle — INSTR(s, sub)

Extracting Parts with POSITION + SUBSTRING

Combine POSITION and SUBSTRING to extract dynamic portions:

-- Extract domain from email (everything after @)
SELECT
  email,
  SUBSTRING(email, POSITION('@' IN email) + 1) AS domain
FROM users;

Combining String Functions

Real data cleaning chains multiple functions. Here’s a pattern we use constantly:

Key Takeaways

• Use || or CONCAT() to combine strings (watch NULL behavior — it’s a silent killer)
• SUBSTRING extracts parts of strings by position (1-indexed, not 0!)
• UPPER/LOWER standardize case — use before comparing or joining on text
• TRIM removes leading/trailing whitespace — suspect it whenever matches fail
• LENGTH returns string length (LEN in MSSQL, and watch trailing spaces)
• POSITION finds where substrings appear — combine with SUBSTRING for extraction

What’s Next

These core functions handle most string work. But sometimes you need heavier machinery: splitting comma-separated values into rows, pattern matching with regular expressions, and extracting the Nth element from delimited data. That’s next.