Yes. In Snowflake, use SPLIT(string, delimiter) to divide a string wherever the exact delimiter occurs; it returns the pieces as an array. If you need one result row per piece instead, use SPLIT_TO_TABLE.
What Snowflake SPLIT Does
SPLIT searches a string for a delimiter and separates the text at each matching occurrence. The delimiter is treated as a literal string, not as a regular expression. For example, a pipe character in SPLIT('red|green|blue', '|') means the actual pipe character, not a regex operator.
The result is an array, rather than a set of rows or a single text value. That distinction matters when choosing how to use the result: arrays are convenient for keeping related pieces together or accessing an element, while rows are usually easier to filter, join, group, and report on. A delimiter can consist of more than one character, so splitting on '--' looks for that complete two-character sequence.
Splitting is useful for simple, consistently formatted values such as a list of tags, a path separated by slashes, or a code made of fixed sections. It is not a general-purpose parser for every text format. In particular, a delimiter-separated string may have quoting or escaping rules that require a more capable parser.
Basic Syntax and Examples
The basic syntax is SPLIT(string, delimiter). Both arguments are expressions that resolve to strings. Here is a standalone example:
SELECT SPLIT('red|green|blue', '|') AS parts;
The result is one array containing three elements: red, green, and blue. To split a column for each record, pass the column name in place of the string:
SELECT customer_id, SPLIT(preference_list, ',') AS preferences
FROM customers;
This still returns one row for each input customer. The preferences column contains an array; the query does not automatically expand it into multiple records. If the value is email,sms,push, the array has those three entries. The rest of the row is not duplicated unless you expand the array or use a table function.
When you need just one known section, SPLIT_PART can be more direct. It returns a selected part as a string, using a one-based part number:
SELECT SPLIT_PART('2026-11-03', '-', 2) AS month;
This returns 11. Use SPLIT when the whole set of pieces is useful; use SPLIT_PART when the query needs a particular position and not the entire array.
Return Each Piece as a Row
A frequent follow-up is how to turn the pieces into separate rows. Snowflake provides SPLIT_TO_TABLE for that purpose. It is a lateral table function, so it can use a value from the row immediately before it in the FROM clause:
SELECT t.order_id, s.index, s.value
FROM orders AS t,
LATERAL SPLIT_TO_TABLE(t.tags, ',') AS s
ORDER BY t.order_id, s.index;
If an order has the tags new,priority,online, the query returns three rows for that order, one per tag. The function provides an INDEX column and a VALUE column, as well as a SEQ value. The index for SPLIT_TO_TABLE starts at 1. Include an ORDER BY when the output needs a predictable display order; SQL does not guarantee row order just because a table function produced indexed pieces.
You can also expand the array returned by SPLIT with FLATTEN:
SELECT t.order_id, f.index, f.value::STRING AS tag
FROM orders AS t,
LATERAL FLATTEN(INPUT => SPLIT(t.tags, ',')) AS f
ORDER BY t.order_id, f.index;
FLATTEN is useful when you already have an array or want its other output fields. Its array index is zero-based, unlike the one-based INDEX from SPLIT_TO_TABLE. That difference is easy to miss when switching between the two approaches.
Choosing Between SPLIT, SPLIT_PART, and SPLIT_TO_TABLE
Choose the function based on what the next step in the query needs, not just on how the input looks.
SPLITreturns an array in the current row. Choose it when you want to retain all pieces together, pass the array to another array operation, or expand it later.SPLIT_PARTreturns one selected part as a string. Choose it for a known position, such as extracting a month from a consistently formatted date-like string.SPLIT_TO_TABLEreturns rows directly. Choose it when each piece should be handled as a separate record, such as filtering individual tags or joining each tag to a lookup table.FLATTENexpands an array into rows. Choose it when the value is already an array or when you need array-specific expansion options.
For example, if a report only needs the second section of a code, splitting the whole string into rows would add unnecessary row expansion. If the goal is to count or classify every tag separately, keeping all tags inside one array can make subsequent relational operations less convenient. A good rule is to keep the value as an array until row-level operations are actually needed.
Empty Values, Spaces, and NULLs
Snowflake does not automatically trim whitespace around each piece. With input such as 'red, green, blue', splitting on a comma leaves a leading space before the second and third values. If downstream comparisons should ignore that space, trim each value after expanding it. For example:
SELECT TRIM(s.value) AS tag
FROM orders AS t,
LATERAL SPLIT_TO_TABLE(t.tags, ',') AS s;
Be deliberate about empty pieces, too. Consecutive delimiters can produce an empty string between them, and a delimiter at the beginning or end can leave an empty piece at that edge. Thus, 'red,,blue,' is not equivalent to a clean three-item list: it contains empty entries. Those entries may affect counts, filters, and joins. If they should be discarded, filter them explicitly, for example with a condition that excludes values whose trimmed text is empty.
A SQL NULL input is different from an empty string. A null value means the source is missing or unknown; an empty string is a present value with no characters. Decide how your query should treat each case rather than replacing one with the other without checking the meaning of the data. When expanding arrays with FLATTEN, its default behavior does not preserve a source row that produces no array elements. The OUTER option can preserve a row in applicable cases, but its null output should be handled intentionally.
To count the pieces in an array, ARRAY_SIZE is often useful:
SELECT ARRAY_SIZE(SPLIT(tags, ',')) AS piece_count
FROM orders;
Check how empty values should count before treating that number as a count of meaningful tags. An empty token is still an element; removing blanks or trimming values may be necessary if the business definition counts only non-empty items.
Literal Delimiters Versus Regular Expressions
SPLIT accepts a literal delimiter. It does not interpret regex patterns such as \s+ as instructions to match arbitrary whitespace. If the input uses several possible separators, normalize them first with a regular-expression function, then split on the normalized delimiter. For example, this converts runs of commas, semicolons, or pipe characters into one comma before splitting:
SELECT SPLIT(REGEXP_REPLACE(raw_text, '[,;|]+', ','), ',') AS parts
FROM source_data;
The character class in the regular expression matches any of the three listed separators, and the plus sign matches one or more in a row. Normalization is appropriate only if those characters genuinely have the same meaning in the data. If separators can occur as ordinary content, a blanket replacement can corrupt values; use a format-aware parser or a more specific rule instead.
Why SPLIT Is Not a CSV Parser
A comma-separated string is not necessarily a properly parsed CSV record. CSV can allow quoted fields, commas inside quoted values, and escaping rules. For example, a field representing Smith, Jane may be quoted so that its comma is part of the field, not a separator. A simple SPLIT(text, ',') still divides at that comma and therefore produces the wrong fields.
If the source is a CSV file, use Snowflake’s file-format and loading features to interpret the CSV structure rather than splitting a raw line yourself. If the value is already stored in a column as text, confirm whether it follows a simple delimiter convention or the full CSV rules. The same caution applies to formats with quoted separators, escaped delimiters, or nested content: splitting text is not a substitute for parsing the format.
Common Problems and Practical Checks
When a split query returns unexpected values, check the input and delimiter together. A delimiter mismatch is common: the source may contain a pipe when the query searches for a comma, or it may use a two-character separator where the query supplies only one. Since the match is literal, a near match is not enough.
Other useful checks include:
- Inspect a few raw source values, including leading and trailing characters, before deciding on the delimiter.
- Check whether whitespace should remain part of each value or be removed with
TRIM. - Look for repeated delimiters and decide whether their empty pieces are valid data or should be filtered.
- Verify whether a missing value is
NULLor an empty string, and handle those cases according to the data’s meaning. - When expanding into rows, order by the source key and split index if display order matters.
- Test values that contain the delimiter as ordinary content before using the result in joins or updates.
Also confirm whether a later join changes the number of rows. Expanding one source record into several rows is expected; joining each resulting piece to a table with duplicate matching keys can multiply the rows again. Counting records before and after expansion, and checking representative examples, helps distinguish intended splitting from an accidental many-to-many join.
Frequently Asked Questions
Does Snowflake SPLIT use a regular expression?
No. SPLIT matches the delimiter literally. Normalize the input with a suitable regular-expression function first if several separator patterns need to be treated alike.
How do I split a string into rows in Snowflake?
Use LATERAL SPLIT_TO_TABLE(column_name, delimiter) in the FROM clause. Its VALUE column contains each piece, and its INDEX column identifies the one-based position.
Does SPLIT remove spaces around each value?
No. It separates at the delimiter but does not trim neighboring spaces. Apply TRIM to each result if spaces are not significant in your data.
Does SPLIT keep empty values between delimiters?
Yes, empty pieces can result from consecutive, leading, or trailing delimiters. Check for them explicitly if your application expects only non-empty values.
Can I use SPLIT to parse a CSV field?
Only when the text is a simple delimiter-separated value with no CSV quoting or escaping requirements. For actual CSV data, use Snowflake’s CSV parsing and file-format support so quoted delimiters are interpreted correctly.
Related
- SolidWorks: Split One Part Into Two Bodies
- Split Feature in SOLIDWORKS: What It Does and How
- Split in SQL Server: STRING_SPLIT and Practical Examples
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Insert Page Break Shortcut Key and How It WorksIn Excel for Microsoft 365 on Windows, press Alt+P, B, I to insert a page break: press the…
- Excel Print Merge File Showing X? FixesIf your Excel print merge file is showing an X instead of the merged cell content, it means…
- How to Sort Google Sheets: Rows, Columns, and FiltersTo sort what people sometimes call Google Excel, open the spreadsheet in Google Sheets, select the full data…
- Excel Very Slow Loading Data? Causes and FixesIf Excel is very slow loading data, first identify whether the delay happens while opening the workbook, recalculating…
- Excel Time Zones: How Date and Time Conversion WorksExcel does not assign a time zone to a date or time value: a worksheet cell holds a…
- Filter Sort in Google Sheets: Sort Data Without Losing RowsTo filter and sort in Google Sheets, select your table, choose Data > Create a filter, then open…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.