String Functions: Intermediate

Slice, search, and reshape text at will

SUBSTR, TRIM, and string surgery 101

Category

SQL

Difficulty

intermediate

Duration

30 minutes

Challenges

0 hands-on challenges

Topics covered: SUBSTRING(), Edge Extractions, REPLACE(), STRPOS(), TRIM()

Lesson Sections

1. SUBSTRING() (concepts: sqlSubstring)

   Basic Extraction Consider product codes that embed category information in specific positions. Extract to String End Practical Applications Parsing Email Domains This query finds the position of the @ symbol, adds 1 to skip past it, then extracts everything from that point to the end. The domain portion has variable length, so omitting the length parameter is essential. Fixed-Width Extraction Many legacy systems use fixed-width formats where each field occupies specific character positions: Best

2. Edge Extractions (concepts: sqlStringBuilding)

   Extract from Start RIGHT() Extracting from the end requires calculating the start position or using negative indices, depending on your database. Extracting from the End Comparison & Best Practices Understanding the differences between extraction methods helps you choose the right approach for each situation. Extraction Patterns Practical Examples Extracting order year and sequence number from order IDs: Extracting Prefixes and Suffixes Extracting from the end of a string with a negative start p

3. REPLACE() (concepts: sqlReplace)

   Basic Replacement Standardizing company names by expanding abbreviations. Removing Characters To remove characters, replace them with an empty string: Advanced Usage Data Standardization Date formats with slashes are converted to ISO format with hyphens. This standardization is critical when importing data from systems with different conventions. Single vs Chained Multiple Replacements Best Practices When to Use REPLACE()

4. STRPOS() (concepts: sqlStrpos)

   Delimiter Positions Extraction by Position Advanced Usage Substring Existence STRPOS returns 0 when not found, but returns 1 for a match at the beginning. Remember that SQL positions start at 1, so a match at the first character returns 1, not 0. Multiple Delimiters Best Practices When to Use STRPOS()

5. TRIM() (concepts: sqlTrim)

   Removing Whitespace Whitespace problems are invisible but cause real bugs. Extra spaces prevent joins from matching and corrupt comparisons. LTRIM() and RTRIM() Custom Character Trim You can specify which characters to remove instead of whitespace: Practical Applications Cleaning Import Data Data imported from spreadsheets or external systems often has invisible whitespace that breaks joins: Import vs Query TRIM Best Practices When to Use TRIM() These are the most common scenarios where whitespa