SUBSTRING in Snowflake: Syntax, Examples, and Tips

How-to basics · Updated November 22, 2026

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 CHARINDEX or POSITION to 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

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.