Text & Regex Functions

SELECT
  LOWER(email) as normalized_email,
  UPPER(country_code) as country,
  INITCAP(full_name) as display_name
FROM users

SELECT
  TRIM('  hello  ') as trimmed,           -- 'hello'
  LTRIM('00123', '0') as no_leading,      -- '123'
  LPAD('42', 5, '0') as padded,           -- '00042'
  RPAD(sku, 10, '-') as fixed_width
FROM products

SELECT
  SUBSTR(phone, 1, 3) as area_code,
  LEFT(description, 100) as preview,
  RIGHT(account_number, 4) as last_four,
  SPLIT_PART(email, '@', 2) as domain
FROM customers

SELECT
  STRPOS(url, '?') as query_start,
  REPLACE(phone, '-', '') as digits_only,
  TRANSLATE(input, 'áéíóú', 'aeiou') as normalized,
  OVERLAY(ssn PLACING '***' FROM 1 FOR 3) as masked
FROM data

SELECT
  CONCAT(first_name, ' ', last_name) as full_name,
  CONCAT_WS(', ', city, state, country) as location,
  '[' || status || ']' as bracketed,
  REPEAT('*', rating) as stars
FROM users

SELECT
  LENGTH(description) as char_count,
  OCTET_LENGTH(payload) as byte_size
FROM messages
WHERE LENGTH(content) > 0

SELECT
  UUID() as id,
  ASCII('A') as code,         -- 65
  CHR(65) as letter,          -- 'A'
  REVERSE('hello') as rev     -- 'olleh'
FROM events

SELECT * FROM logs
WHERE REGEXP_LIKE(message, 'error|warning', 'i')  -- Case insensitive

SELECT
  REGEXP_MATCH(log_line, 'user=(\w+)')[1] as username
FROM logs

SELECT
  REGEXP_REPLACE(phone, '[^0-9]', '') as digits_only,
  REGEXP_REPLACE(text, '\s+', ' ') as single_spaces,
  REGEXP_REPLACE(email, '@.*', '@***') as masked_email
FROM contacts

SELECT REGEXP_REPLACE(text, 'foo', 'bar', 'gi')  -- All occurrences, case insensitive

SELECT
  LEVENSHTEIN(input, 'expected') as distance,
  TO_HEX(color_code) as hex_color
FROM data
WHERE LEVENSHTEIN(search_term, product_name) < 3  -- Fuzzy match

SELECT
  signup_id,
  LOWER(TRIM(email)) as email,
  REGEXP_REPLACE(phone, '[^0-9+]', '') as phone,
  INITCAP(TRIM(name)) as name
FROM signups

SELECT
  view_id,
  SPLIT_PART(url, '/', 3) as domain,
  REGEXP_MATCH(url, '/product/(\d+)')[1] as product_id,
  SPLIT_PART(SPLIT_PART(url, '?', 2), '&', 1) as first_param
FROM page_views

SELECT
  customer_id,
  CONCAT(LEFT(email, 2), '***@', SPLIT_PART(email, '@', 2)) as masked_email,
  CONCAT('***-**-', RIGHT(ssn, 4)) as masked_ssn,
  OVERLAY(credit_card PLACING '****-****-****-' FROM 1 FOR 15) as masked_card
FROM customers

SELECT
  log_id,
  REGEXP_MATCH(log_line, 'user=(\w+)')[1] as username,
  REGEXP_MATCH(log_line, 'action=(\w+)')[1] as action,
  CASE
    WHEN REGEXP_LIKE(log_line, 'ERROR', 'i') THEN 'error'
    WHEN REGEXP_LIKE(log_line, 'WARN', 'i') THEN 'warning'
    ELSE 'info'
  END as level
FROM logs