SQL String Functions

-- CONCAT function (works everywhere)
SELECT CONCAT(first_name, ' ', last_name) AS full_name
FROM employees;

-- || operator (PostgreSQL, Snowflake, SQLite)
SELECT first_name || ' ' || last_name AS full_name
FROM employees;

-- + operator (SQL Server only)
SELECT first_name + ' ' + last_name AS full_name
FROM employees;

-- CONCAT_WS: concat with separator (avoids repeated separators)
SELECT CONCAT_WS(', ', city, state, country) AS location
FROM addresses;
-- Result: 'Austin, TX, US'

NULL trap: CONCAT('hello', NULL) returns NULL in standard SQL. Use COALESCE to guard. CONCAT_WS handles NULLs better — it skips NULL arguments.

-- SUBSTRING(string, start, length)
SELECT SUBSTRING('DataDriven', 1, 4);   -- 'Data'
SELECT SUBSTRING('DataDriven', 5, 6);   -- 'Driven'
SELECT SUBSTRING('2024-01-15', 1, 7);   -- '2024-01'

-- Extract area code from phone number
SELECT SUBSTRING(phone, 2, 3) AS area_code
FROM contacts
WHERE phone LIKE '(%';
-- '(512) 555-1234' -> '512'

-- Alternative syntax (PostgreSQL, standard SQL)
SELECT SUBSTRING('DataDriven' FROM 1 FOR 4);  -- 'Data'

Second most common string function in the corpus. The first character lives at position 1, not 0 — that off-by-one catches Python-native engineers about 15% of the time in mock interviews. Name the convention out loud before you write the query.

-- Remove whitespace
SELECT TRIM('  hello  ');          -- 'hello'
SELECT LTRIM('  hello  ');         -- 'hello  '
SELECT RTRIM('  hello  ');         -- '  hello'

-- Remove specific characters
SELECT TRIM(BOTH '-' FROM '--hello--');  -- 'hello'
SELECT TRIM(LEADING '0' FROM '00042');   -- '42'

-- Common pipeline pattern: clean and normalize
SELECT
  TRIM(UPPER(country_code)) AS country_code,
  TRIM(LOWER(email)) AS email,
  TRIM(name) AS name
FROM raw_users;

Pipeline tip: always TRIM string columns from raw data before joining or comparing. A common data quality bug is 'US' failing to match 'US ' (with trailing space) in a JOIN. TRIM both sides to prevent.

-- Basic replacement
SELECT REPLACE('Hello World', 'World', 'SQL');  -- 'Hello SQL'

-- Remove characters (replace with empty string)
SELECT REPLACE('(512) 555-1234', '-', '');  -- '(512) 5551234'
SELECT REPLACE(REPLACE('(512) 555-1234', '(', ''), ')', '');
-- '512 555-1234'

-- Clean currency strings for numeric conversion
SELECT
  CAST(
    REPLACE(REPLACE(price_text, '$', ''), ',', '')
    AS DECIMAL(10, 2)
  ) AS price_numeric
FROM raw_products;
-- '$1,299.99' -> 1299.99

-- LENGTH
SELECT LENGTH('DataDriven');  -- 10
SELECT LENGTH('');            -- 0
SELECT LENGTH(NULL);          -- NULL

-- Note: SQL Server uses LEN() and trims trailing spaces
-- BigQuery uses LENGTH() but also has CHAR_LENGTH()

-- UPPER and LOWER
SELECT UPPER('hello');  -- 'HELLO'
SELECT LOWER('HELLO');  -- 'hello'

-- Case-insensitive comparison
SELECT *
FROM users
WHERE LOWER(email) = LOWER('User@Example.COM');

-- Data validation: find suspiciously short/long values
SELECT *
FROM products
WHERE LENGTH(product_name) < 2
   OR LENGTH(product_name) > 200;

-- SPLIT_PART(string, delimiter, position)
SELECT SPLIT_PART('2024-01-15', '-', 1);  -- '2024'
SELECT SPLIT_PART('2024-01-15', '-', 2);  -- '01'
SELECT SPLIT_PART('2024-01-15', '-', 3);  -- '15'

-- Extract domain from email
SELECT SPLIT_PART(email, '@', 2) AS domain
FROM users;
-- 'user@gmail.com' -> 'gmail.com'

-- Extract path segments from URL
SELECT
  SPLIT_PART(url_path, '/', 2) AS section,
  SPLIT_PART(url_path, '/', 3) AS page
FROM page_views;
-- '/blog/sql-joins' -> section='blog', page='sql-joins'

-- MySQL alternative: SUBSTRING_INDEX
-- SELECT SUBSTRING_INDEX('user@gmail.com', '@', -1);  -- 'gmail.com'

Available in PostgreSQL, Snowflake, Redshift. BigQuery uses SPLIT() returning an array. MySQL: SUBSTRING_INDEX.

-- REGEXP_REPLACE: replace pattern matches
-- Remove all non-numeric characters from phone numbers
SELECT REGEXP_REPLACE('(512) 555-1234', '[^0-9]', '', 'g');
-- '5125551234'

-- REGEXP_SUBSTR / REGEXP_EXTRACT: extract matching text
-- PostgreSQL: SUBSTRING with regex
SELECT SUBSTRING('Order #12345 confirmed' FROM '#([0-9]+)');
-- '12345'

-- Snowflake: REGEXP_SUBSTR
SELECT REGEXP_SUBSTR('Order #12345 confirmed', '#([0-9]+)', 1, 1, 'e');
-- '12345'

-- BigQuery: REGEXP_EXTRACT
SELECT REGEXP_EXTRACT('Order #12345 confirmed', r'#([0-9]+)');
-- '12345'

-- REGEXP_LIKE / ~ : test if string matches pattern
-- PostgreSQL
SELECT * FROM logs WHERE message ~ 'ERROR|FATAL';

-- Snowflake
SELECT * FROM logs WHERE REGEXP_LIKE(message, 'ERROR|FATAL');

-- BigQuery
SELECT * FROM logs WHERE REGEXP_CONTAINS(message, r'ERROR|FATAL');

Performance warning: regex is significantly slower than simple string functions. On large tables, REGEXP_REPLACE per row can add minutes. Use simple functions (REPLACE, SPLIT_PART) when possible. Save regex for patterns that can't be expressed otherwise.

-- Create a surrogate key from multiple columns
SELECT
  MD5(CONCAT(
    COALESCE(CAST(user_id AS TEXT), ''),
    '|',
    COALESCE(CAST(event_date AS TEXT), ''),
    '|',
    COALESCE(event_type, '')
  )) AS event_key,
  *
FROM events;

-- Parse key=value pairs from log entries
-- Input: 'user=alice action=login ip=10.0.0.1'
SELECT
  SPLIT_PART(
    SPLIT_PART(log_line, 'user=', 2), ' ', 1
  ) AS username,
  SPLIT_PART(
    SPLIT_PART(log_line, 'action=', 2), ' ', 1
  ) AS action,
  SPLIT_PART(
    SPLIT_PART(log_line, 'ip=', 2), ' ', 1
  ) AS ip_address
FROM access_logs;

-- Normalize company names for deduplication
SELECT
  TRIM(
    REGEXP_REPLACE(
      LOWER(company_name),
      '\s+(inc\.?|llc|corp\.?|ltd\.?)$',
      '',
      'i'
    )
  ) AS normalized_name,
  company_name AS original_name
FROM companies;
-- 'Acme Corp.' -> 'acme'
-- 'ACME Inc'   -> 'acme'
-- 'acme LLC'   -> 'acme'