The SUBSTRING function in Snowflake extracts a portion of a string starting at a specified position and continuing for a specified length. Its syntax is SUBSTRING(expr, start_position [, length]), and it returns the extracted substring as a string.
Background: What SUBSTRING Does in SQL
String manipulation is a core need in SQL—parsing codes, cleaning free-text fields, or splitting identifiers. Snowflake follows the ANSI SQL standard with its SUBSTRING function, which is equivalent to SUBSTR (both are aliases). The function works on any string expression, including column values, literals, or the result of another function. It is case-sensitive and does not modify the original string; it returns a new string.
How SUBSTRING Works in Snowflake
The function takes one required argument (the string) and two optional ones (position and length). The position is 1-based, meaning the first character is at position 1. If you omit the length, it returns everything from the start position to the end of the string. Negative start positions are allowed and count from the end of the string (e.g., -1 is the last character).
Basic examples:
SELECT SUBSTRING('Snowflake', 1, 4);returns'Snow'.SELECT SUBSTRING('Snowflake', 5);returns'flake'.SELECT SUBSTRING('Snowflake', -4);returns'lake'(last 4 characters).SELECT SUBSTRING('Snowflake', 0, 3);returns'Sno'—position 0 is treated as 1.
If the start position is beyond the string length, the result is an empty string. If the length is negative, Snowflake returns NULL.
Choosing Between SUBSTRING, LEFT, and RIGHT
Snowflake also offers LEFT and RIGHT functions for extracting from the beginning or end. Use LEFT(string, n) to get the first n characters, and RIGHT(string, n) for the last n. These are simpler when you always want a prefix or suffix. However, SUBSTRING is more flexible because it allows any starting point. For example, to get characters 3 through 7, you must use SUBSTRING. In terms of performance, all three are efficient; choose based on readability.
Common Use Cases and Real Examples
Here are practical scenarios where you might use SUBSTRING:
- Extracting a date part from a YYYYMMDD string:
SELECT SUBSTRING('20250315', 1, 4) AS year;returns'2025'. - Getting a domain from an email:
SELECT SUBSTRING('user@example.com', POSITION('@' IN 'user@example.com') + 1);returns'example.com'. - Cleaning a fixed-width column: if a code is always 10 characters and you need the middle 4, use
SUBSTRING(code, 4, 4). - Combining with
CHARINDEXorPOSITIONto find a delimiter first, then extract after it.
Handling NULLs and Edge Cases
If any argument is NULL, the function returns NULL. This is standard for Snowflake. Be careful with non-string inputs: Snowflake implicitly converts numbers and dates to strings before applying SUBSTRING, but it’s safer to cast explicitly with TO_CHAR or ::VARCHAR. For example, SELECT SUBSTRING(12345::VARCHAR, 2, 3); returns '234'. Also, the function works with binary data (VARBINARY), but the position and length refer to bytes, not characters, which can be tricky with multi-byte UTF-8 characters.
Performance and Best Practices
Using SUBSTRING in a WHERE clause can prevent index usage if the column is indexed, because the function must be evaluated on every row. However, Snowflake does not use traditional indexes; it uses micro-partitions and pruning. Still, applying SUBSTRING to a column in a filter may force a full scan of the partition. To optimize, consider storing extracted values as separate columns during data loading if you filter on them often. Also, avoid using SUBSTRING on very large strings unnecessarily; it’s generally fast, but it does allocate memory for the result.
Comparing SUBSTRING Across Databases
Snowflake’s SUBSTRING is similar to that in PostgreSQL, MySQL, and SQL Server, but there are differences in position semantics. In SQL Server, SUBSTRING is also 1-based, but negative start positions are not allowed. In Oracle, SUBSTR allows negative positions but treats 0 as 1 (like Snowflake). MySQL’s SUBSTRING also allows negative positions. So if you migrate from SQL Server, you’ll need to adjust any negative start positions. Also, Snowflake’s SUBSTRING is case-sensitive for string content, but the function name itself is case-insensitive.
Frequently Asked Questions
What is the difference between SUBSTRING and SUBSTR in Snowflake?
There is no difference; they are synonyms. Snowflake documentation lists SUBSTR as an alias for SUBSTRING. You can use either, and they behave identically.
Can I use SUBSTRING on a column of type VARIANT?
Yes, but you need to cast the VARIANT to a string first. For example, SELECT SUBSTRING(data:name::VARCHAR, 1, 5) FROM table; extracts the first 5 characters of the ‘name’ field. Without the cast, Snowflake will try to convert the VARIANT to a string implicitly, but it’s safer to be explicit.
How do I extract a substring after a specific character?
Combine SUBSTRING with POSITION or CHARINDEX. For instance, to get everything after the first hyphen: SELECT SUBSTRING('abc-123', POSITION('-' IN 'abc-123') + 1); returns '123'. If the character is not found, POSITION returns 0, so the substring starts at 1, giving the whole string.
Does SUBSTRING work with Unicode and emoji?
Yes, but be aware that Snowflake counts characters, not bytes, for VARCHAR strings. So an emoji like 😊 counts as one character. However, if you use SUBSTRING on a VARBINARY value, it counts bytes, which could split a multi-byte character. For VARCHAR, it’s safe.
What happens if the length is longer than the remaining string?
Snowflake simply returns the remaining characters from the start position to the end of the string. It does not error or pad with spaces. For example, SUBSTRING('Snow', 2, 10) returns 'now'.
Can I use SUBSTRING in a JOIN condition?
Yes, you can use it in a JOIN condition, but be cautious about performance. For example, joining on SUBSTRING(a.code, 1, 3) = b.prefix works, but it may be slower than joining on a pre-computed column. Consider adding a generated column if you do this frequently.
Related
- JavaScript String to Int: parseInt, Number, and More
- JavaScript String Objects: What They Are and How They Work
- Split a Column in Snowflake: SQL Methods and Examples
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- SharePoint Excel: How to Group Dates in a PivotTableTo group SharePoint data by date in an Excel PivotTable, open the workbook in Excel for Microsoft 365…
- Untitled Spreadsheet in Google Sheets: What It MeansAn “Untitled spreadsheet” in Google Sheets is a new spreadsheet file that has not been given a custom…
- Excel Custom Number Format: Add Leading ZerosTo show leading zeros in Excel without changing a number’s value, apply a custom number format: select the…
- 0.3-Inch Hanging Indent in Word: Set It Up PreciselyTo create a 0.3-inch hanging indent in Word, select the paragraph or paragraphs, open the Paragraph dialog, choose…
- Google Sheets Conditional Formatting for Full RowsYes. In Google Sheets, you can use conditional formatting to highlight an entire row when a cell in…
- Downloadable Budget Spreadsheet: Find and Use a TemplateYes. You can download a budget spreadsheet template from Excel’s template gallery or Microsoft Create, then save a…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.