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

• Concatenate:
  • PostgreSQL uses || or CONCAT()
  • MySQL uses CONCAT()
  • MSSQL uses + or CONCAT()
  • Oracle uses || or CONCAT()
• With NULL:
  • PostgreSQL’s || with NULL returns NULL
  • MySQL’s CONCAT('a', NULL) returns 'a'
  • MSSQL’s 'a' + NULL returns NULL
  • Oracle’s || with NULL returns 'a'

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 uses SUBSTRING(s, start, len)
  • MySQL uses SUBSTRING(s, start, len)
  • MSSQL uses SUBSTRING(s, start, len)
  • Oracle uses SUBSTR(s, start, len)
• Left N chars:
  • PostgreSQL uses LEFT(s, n)
  • MySQL uses LEFT(s, n)
  • MSSQL uses LEFT(s, n)
  • Oracle uses SUBSTR(s, 1, n)
• Right N chars:
  • PostgreSQL uses RIGHT(s, n)
  • MySQL uses RIGHT(s, n)
  • MSSQL uses RIGHT(s, n)
  • Oracle uses SUBSTR(s, -n)

Case Conversion: UPPER and LOWER

Case inconsistency is the one of the most common causes 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 uses LENGTH
  • MySQL uses LENGTH
  • MSSQL uses LEN
  • Oracle uses LENGTH

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 uses POSITION(sub IN s)
  • MySQL uses LOCATE(sub, s)
  • MSSQL uses CHARINDEX(sub, s)
  • Oracle uses 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 80% of 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.