UPPER / LOWER / TRIM / LENGTH / REPLACE
Category: String Functions
Platform Support:
✅ Snowflake | ✅ BigQuery | ✅ Databricks
Description
Common string manipulation functions. UPPER converts to uppercase, LOWER to lowercase, TRIM removes whitespace, LENGTH returns string length, and REPLACE substitutes text. Essential for text cleaning, standardization, and data quality.
Syntax
UPPER(string_expression)
LOWER(string_expression)
TRIM([LEADING | TRAILING | BOTH] [characters FROM] string_expression)
LENGTH(string_expression)
REPLACE(string_expression, search_string, replacement_string)Platform-Specific Notes
All Platforms:
- Standard SQL string functions
- Nearly identical syntax and behavior
- Work with VARCHAR/STRING/TEXT types
- Handle NULL inputs (return NULL)
Snowflake:
- CHAR_LENGTH as synonym for LENGTH
- LTRIM, RTRIM for directional trimming
- Case-sensitive by default
BigQuery:
- LENGTH returns bytes for BYTES type
- CHAR_LENGTH for character count
- TRIM has flexible syntax
Databricks:
- Standard Spark SQL functions
- LTRIM, RTRIM available
- Compatible with Hive
Example 1: UPPER / LOWER - Case Conversion
All Platforms:
SELECT
customer_name,
UPPER(customer_name) as name_uppercase,
LOWER(customer_name) as name_lowercase,
UPPER(email) as email_uppercase,
LOWER(email) as email_lowercase,
-- Proper case (first letter uppercase)
CONCAT(
UPPER(SUBSTRING(customer_name, 1, 1)),
LOWER(SUBSTRING(customer_name, 2))
) as name_proper_case
FROM customers;Sample Data (customers table):
customer_id | customer_name | |
|---|---|---|
1 | john SMITH | |
2 | MARIA garcia | |
3 | Sarah Johnson |
Result:
customer_name | name_uppercase | name_lowercase | email_uppercase | email_lowercase | name_proper_case |
|---|---|---|---|---|---|
john SMITH | JOHN SMITH | john smith | John smith | ||
MARIA garcia | MARIA GARCIA | maria garcia | Maria garcia | ||
Sarah Johnson | SARAH JOHNSON | sarah johnson | Sarah johnson |
Example 2: TRIM - Remove Whitespace
All Platforms:
SELECT
product_code,
LENGTH(product_code) as original_length,
TRIM(product_code) as trimmed,
LENGTH(TRIM(product_code)) as trimmed_length,
-- Remove specific characters
TRIM(BOTH '-' FROM product_code) as trim_dashes,
-- Left trim only
LTRIM(product_code) as left_trimmed,
-- Right trim only
RTRIM(product_code) as right_trimmed
FROM products;Sample Data (products table):
product_id | product_code |
|---|---|
1 | " ABC123 " |
2 | "XYZ789 " |
3 | " -PROD- " |
Result:
product_code | original_length | trimmed | trimmed_length | trim_dashes | left_trimmed | right_trimmed |
|---|---|---|---|---|---|---|
" ABC123 " | 10 | "ABC123" | 6 | "ABC123" | "ABC123 " | " ABC123" |
"XYZ789 " | 9 | "XYZ789" | 6 | "XYZ789" | "XYZ789 " | "XYZ789" |
" -PROD- " | 10 | "-PROD-" | 6 | " PROD " | "-PROD- " | " -PROD-" |
Example 3: LENGTH - String Length
All Platforms:
SELECT
description,
LENGTH(description) as char_count,
LENGTH(TRIM(description)) as char_count_trimmed,
-- Check for empty or whitespace-only strings
CASE
WHEN LENGTH(TRIM(description)) = 0 THEN 'Empty'
WHEN LENGTH(TRIM(description)) < 10 THEN 'Short'
WHEN LENGTH(TRIM(description)) < 50 THEN 'Medium'
ELSE 'Long'
END as description_length_category,
-- Truncate long descriptions
CASE
WHEN LENGTH(description) > 50
THEN SUBSTRING(description, 1, 47) || '...'
ELSE description
END as truncated_description
FROM products;Sample Data (products table):
product_id | description |
|---|---|
1 | "Laptop" |
2 | "High-performance wireless mouse with ergonomic design" |
3 | " " |
4 | NULL |
Result:
description | char_count | char_count_trimmed | description_length_category | truncated_description |
|---|---|---|---|---|
Laptop | 6 | 6 | Short | Laptop |
High-performance... | 54 | 54 | Long | High-performance wireless mouse with ergo... |
" " | 3 | 0 | Empty | " " |
NULL | NULL | NULL | NULL | NULL |
Example 4: REPLACE - Text Substitution
All Platforms:
SELECT
phone_number,
-- Remove formatting characters
REPLACE(REPLACE(REPLACE(phone_number, '(', ''), ')', ''), '-', '') as clean_phone,
-- Standardize format
REPLACE(REPLACE(phone_number, '.', '-'), '/', '-') as standardized_phone,
-- Mask sensitive data
CONCAT(
SUBSTRING(phone_number, 1, 3),
REPLACE(SUBSTRING(phone_number, 4), SUBSTRING(phone_number, 4, LENGTH(phone_number) - 6), 'XXX')
) as masked_phone
FROM contacts;Sample Data (contacts table):
contact_id | phone_number |
|---|---|
1 | (555) 123-4567 |
2 | 555.987.6543 |
3 | 555/456/7890 |
Result:
phone_number | clean_phone | standardized_phone | masked_phone |
|---|---|---|---|
(555) 123-4567 | 5551234567 | 555-123-4567 | 555XXX4567 |
555.987.6543 | 5559876543 | 555-987-6543 | 555XXX6543 |
555/456/7890 | 5554567890 | 555-456-7890 | 555XXX7890 |
Example 5: Data Cleaning Pipeline
All Platforms:
SELECT
customer_name_raw,
-- Clean and standardize name
TRIM(
UPPER(
REPLACE(
REPLACE(
REPLACE(customer_name_raw, ' ', ' '), -- Double spaces to single
',', ''), -- Remove commas
'.', '') -- Remove periods
)
) as customer_name_clean,
email_raw,
-- Clean and standardize email
LOWER(TRIM(email_raw)) as email_clean,
-- Validate email format (basic check)
CASE
WHEN LOWER(TRIM(email_raw)) LIKE '%@%.%'
AND LENGTH(TRIM(email_raw)) > 5
THEN 'Valid'
ELSE 'Invalid'
END as email_status
FROM customer_import;Sample Data (customer_import table):
customer_id | customer_name_raw | email_raw |
|---|---|---|
1 | " John Smith " | " John@Email.COM " |
2 | "Garcia, Maria" | "MARIA@company" |
3 | "Dr. Sarah Johnson" |
Result:
customer_name_raw | customer_name_clean | email_raw | email_clean | email_status |
|---|---|---|---|---|
" John Smith " | "JOHN SMITH" | " John@Email.COM " | Valid | |
"Garcia, Maria" | "GARCIA MARIA" | "MARIA@company" | "maria@company" | Invalid |
"Dr. Sarah Johnson" | "DR SARAH JOHNSON" | Valid |
Example 6: Combining All Functions
All Platforms:
SELECT
product_name,
-- Create SKU from product name
CONCAT(
UPPER(SUBSTRING(REPLACE(TRIM(product_name), ' ', ''), 1, 3)),
'-',
LPAD(CAST(product_id AS VARCHAR), 5, '0')
) as generated_sku,
-- Clean description
CASE
WHEN LENGTH(TRIM(description)) > 0 THEN
CONCAT(
UPPER(SUBSTRING(TRIM(description), 1, 1)),
LOWER(SUBSTRING(TRIM(description), 2))
)
ELSE 'No description'
END as clean_description,
-- Category standardization
UPPER(REPLACE(TRIM(category), '_', ' ')) as category_standardized
FROM products;Sample Data:
product_id | product_name | description | category |
|---|---|---|---|
1 | " Wireless Mouse " | " ERGONOMIC DESIGN" | "computer_accessories" |
2 | "USB Cable" | "" | "cables_adapters" |
Result:
product_name | generated_sku | clean_description | category_standardized |
|---|---|---|---|
" Wireless Mouse " | "WIR-00001" | "Ergonomic design" | "COMPUTER ACCESSORIES" |
"USB Cable" | "USB-00002" | "No description" | "CABLES ADAPTERS" |
Common String Function Patterns
Email standardization:
LOWER(TRIM(email)) as clean_emailRemove multiple spaces:
REGEXP_REPLACE(text, '\s+', ' ') -- Replace multiple whitespace with single spacePhone number cleaning:
REPLACE(REPLACE(REPLACE(REPLACE(phone, '(', ''), ')', ''), '-', ''), ' ', '')Title case (first letter of each word):
-- Platform-specific, or use INITCAP in some platforms
CONCAT(
UPPER(SUBSTRING(word, 1, 1)),
LOWER(SUBSTRING(word, 2))
)Use Cases by Function
UPPER/LOWER:
- Case-insensitive comparison: WHERE UPPER(email) = UPPER(@search)
- Standardization: Consistent data format
- Display formatting: UI requirements
- Data matching: Fuzzy matching
TRIM:
- Data cleaning: Remove accidental whitespace
- Import validation: Clean uploaded data
- Comparison: Ensure accurate matching
- Storage optimization: Remove unnecessary characters
LENGTH:
- Validation: Check min/max lengths
- Truncation: Decide if text needs shortening
- Data quality: Find empty or suspiciously short/long values
- Categorization: Group by text length
REPLACE:
- Formatting removal: Clean phone numbers, codes
- Character substitution: Standardize separators
- Censoring: Mask sensitive data
- Data correction: Fix common typos
Performance Considerations
- Avoid in WHERE clauses: Prevents index usage
- Pre-compute when possible: Create computed columns
- Bulk operations: Apply transformations during ETL
- Regular expressions: Use for complex patterns (but slower)
Best Practices
- Normalize during insert: Clean data at entry point
- Store cleaned versions: Separate columns for display vs search
- Handle NULLs: Use COALESCE for NULL strings
- Test edge cases: Empty strings, special characters, Unicode
- Document transformations: Explain cleaning logic
- Validate after cleaning: Ensure data quality
- Use consistent casing: Pick UPPER or LOWER for standards
- Chain functions carefully: Test each step independently
Interested to Learn More? Try Out the Free 14-Days Trial
One AI-native workspace to build, ship, and monitor dbt™ — without the tool sprawl.