String Functions: Intermediate

Extract portions of text by position

SUBSTRING extracts a portion of a string starting at a specified position for a specified length. It allows you to pull out exactly the characters you need from any position within the text. This function is fundamental for parsing structured data embedded in strings.

The syntax is SUBSTRING(string, start_position, length). The start_position indicates where extraction begins (1-indexed, meaning the first character is position 1). The length specifies how many characters to extract. If length is omitted, extraction continues to the end of the string.

Understanding how positions and lengths work in SUBSTRING is key to extracting exactly the characters you need.

SUBSTRING(product_code, 1, 4) extracts 4 characters starting at position 1, returning the category code. SUBSTRING(product_code, 6, 4) extracts 4 characters starting at position 6, returning the year. Position counting starts at 1, not 0.

When you omit the length parameter, SUBSTRING extracts from the start position to the end of the string:

SUBSTRING becomes powerful when combined with other functions like STRPOS to handle variable-length data.

SUBSTRING combined with STRPOS can extract variable-length portions of strings:

Each field is extracted by its position and length. TRIM removes the padding spaces from the name fields. This pattern is common when migrating data from mainframe systems.

SUBSTRING excels at structured parsing tasks but requires careful attention to position counting.

SUBSTRING is ideal for data with predictable structure where you know exactly where values are located.

Structured Identifiers

Extract embedded codes from product IDs, order numbers, or SKUs

Fixed-Width Parsing

Parse legacy record formats with fixed character positions

Composite Keys

Break apart composite keys into individual components

URLs and Paths

Extract portions of URLs, file paths, or address strings

Understanding SUBSTRING execution helps you predict behavior with edge cases like NULL inputs or out-of-range positions.

SELECT
  ___(product_code, ___, ___) AS category
FROM products

SUBSTRING with a fixed start position and length is the right tool when your data follows a consistent structure where each field always occupies the same character positions.

Combining SUBSTRING with STRPOS lets you handle variable-length strings by first finding a delimiter and then extracting relative to that location rather than relying on fixed positions.

Pull text from the start or end of values

Extracting characters from the beginning or end of a string is a common operation. SUBSTR handles both cases. To get the first N characters, use SUBSTR(string, 1, N). To get the last N characters, use a negative start position: SUBSTR(string, -N).

Dedicated prefix/suffix functions are found in some databases, but Presto provides unified handling through SUBSTR with position arguments. This single function replaces the need for multiple specialized functions, giving you consistent syntax across all extraction patterns.

Using SUBSTR from position 1 with a specified length captures the prefix portion of any string with clarity and consistency.

SUBSTR(string, 1, N) extracts the first N characters, ideal for fixed-length prefixes like country codes, category identifiers, or version numbers:

SUBSTR(phone_number, 1, 3) extracts the first 3 characters, capturing the country code prefix. The 1 indicates starting at the first character, and 3 is the length to extract.

SUBSTR with a negative start position extracts from the end of a string, useful for file extensions, trailing codes, or check digits:

SUBSTR(filename, -3) extracts the last 3 characters. The negative position -3 means "start 3 characters from the end." For fixed-length extensions like .pdf or .csv, this works perfectly. For variable-length extensions, you would need STRPOS to find the dot.

Compare how different SUBSTR calls extract from the beginning, end, or middle of a string.

To get the first N characters, use SUBSTR(str, 1, N). To get the last N characters, use SUBSTR(str, -N). These patterns handle all the use cases needed for extracting fixed-width prefixes and suffixes from standardized identifiers.

Prefixes (the leading portion of a string) and suffixes (the trailing portion) are common in structured data. Country codes, file extensions, year prefixes, check digits, and category codes are all prefixes or suffixes that can be extracted using SUBSTR.

SUBSTR execution differs slightly from SUBSTRING in some databases, particularly with negative positions.

SELECT
  ___(product_code, ___, ___) AS category
FROM products

When the position of a delimiter is unknown in advance, combining STRPOS to locate it and SUBSTR to extract around it produces a robust parser that works correctly regardless of how long the prefix or suffix is.

Swap characters or words inside text

REPLACE finds all occurrences of a substring and replaces them with a different substring. Syntax: REPLACE(string, search_string, replacement_string). The comparison is case-sensitive, and every match is replaced globally throughout the string.

REPLACE is essential for data cleaning, standardization, and text transformation workflows.

Every occurrence of 'Corp.' is replaced with 'Corporation'. The third row shows that REPLACE works even when the search string appears in the middle. Rows without matches are unchanged.

Chaining REPLACE calls removes multiple different characters. Each call removes one type of character. The result contains only digits and spaces (spaces would require another REPLACE to remove).

REPLACE becomes more powerful when chained together or combined with other string functions.

REPLACE is commonly used to convert between data formats or fix inconsistent input:

A single REPLACE handles one substitution. For multiple characters, you chain calls. Compare the approaches:

For multiple different replacements, nest REPLACE calls:

Each REPLACE() transforms one code. The innermost replacement runs first, then the next, and so on. Order matters if replacements might overlap.

REPLACE is a workhorse function for data cleaning, but knowing when to use it versus other approaches matters.

Compare your data before and after applying REPLACE to understand its impact on consistency.

REPLACE performs a global search-and-replace in a single pass, making it efficient for bulk text transformations.

Data standardization is one of the most common real-world applications of REPLACE.

SELECT
  ___(product_name, ___, ___) AS clean_name
FROM products

REPLACE substitutes every occurrence in one pass, not just the first. This global behavior is what makes it effective for bulk data cleaning across entire columns.

Nesting REPLACE() calls is the standard approach for removing or substituting multiple different characters in a single query.

Locate where a pattern appears in text

STRPOS finds the starting position of a substring within a larger string. Syntax: STRPOS(string, substring). Returns the 1-indexed position of the first match, or 0 if not found.

STRPOS is crucial for locating delimiters, parsing variable-format strings, and building conditional logic based on string contents. The position it returns can then be used directly in other string functions.

STRPOS is essential for parsing strings with delimiters.

STRPOS() returns where the @ symbol appears. The position varies by email length. In 'alice@gmail.com', @ is at position 6. In 'bob@company.org', @ is at position 4.

Combine STRPOS with SUBSTRING to extract variable-length portions:

The username is extracted from position 1 to just before the @. The domain is extracted from just after the @ to the end. STRPOS provides the exact location needed for these calculations.

Beyond simple lookups, STRPOS enables pattern-based logic and complex parsing strategies.

STRPOS returns 0 when the substring is not found, making it useful for conditional logic:

If STRPOS returns a value greater than 0, the substring exists. This pattern enables classification based on string contents without exact matching.

When parsing complex strings with multiple delimiters, STRPOS helps locate each one:

STRPOS finds the first occurrence only. For paths with multiple slashes, you would extract portions sequentially or use database-specific functions for splitting.

STRPOS shines in data parsing tasks where you need to locate dynamic boundaries within strings.

These are the most common scenarios where STRPOS proves invaluable.

Locate Delimiters

Find the position of separators for substring extraction

Pattern Checking

Check if a string contains a specific pattern

Variable-Format Parsing

Parse emails, URLs, or other dynamic-length data

Conditional Logic

Make decisions based on string contents

STRPOS scans from the start of the string, so its performance depends on where matches occur.

SELECT
  ___(email, ___) AS at_pos
FROM users

STRPOS returns 0 when the substring is not found. This means STRPOS(col, 'x') > 0 reliably tests for presence, while comparing the result to NULL would never match.

The most powerful use of STRPOS() is feeding its result to SUBSTRING(): by finding a delimiter position dynamically, you can extract variable-length substrings that depend on the actual content of each row.

Remove unwanted whitespace from values

TRIM removes unwanted characters from the beginning, end, or both sides of a string. By default it removes whitespace. Syntax: TRIM(string) or TRIM(LEADING/TRAILING/BOTH characters FROM string) for custom characters. LTRIM and RTRIM are shortcuts.

TRIM is essential for cleaning user input and ensuring accurate string comparisons, especially when dealing with legacy data that has inconsistent whitespace.

TRIM removes spaces from both ends. All usernames are now clean and comparable. Without trimming, ' alice ' would not match 'alice' in a WHERE clause or JOIN.

LTRIM removes leading characters only. RTRIM removes trailing characters only. These are useful when you need asymmetric cleaning:

TRIM(LEADING '$' FROM ...) removes dollar signs from the beginning. Note that the second row has two dollar signs, and only the leading ones are removed. TRIM removes all consecutive matching characters from the specified side.

TRIM is a staple in data pipelines and ETL workflows. Here are common patterns you will encounter.

When joins mysteriously return no results, whitespace is often the culprit. Applying TRIM() to both sides of the join ensures spaces do not prevent matches.

You can apply TRIM when data is loaded or when it is queried. Each approach has trade-offs for performance and data integrity.

TRIM is one of the most frequently used functions in data cleaning pipelines. Here is when to apply it.

TRIM is highly efficient because it only examines characters at the string edges, not the entire content.

Invisible whitespace bugs are notoriously hard to debug, making TRIM an essential defensive practice.

SELECT
  ___(username) AS clean_name
FROM users