Yes. In Snowflake, use SPLIT_PART(text, delimiter, position) to place known pieces of a string into separate SQL columns; use SPLIT to create an array, or combine it with FLATTEN when you need one row per piece. The right method depends on whether the number of output fields is fixed or varies by row.
Use SPLIT_PART for a fixed set of columns
SPLIT_PART is usually the most direct choice when every record should produce the same named columns. It takes a source string, an exact delimiter, and the position of the piece to return. Positions start at 1, so position 1 means the first piece and position 2 means the second.
For example, if a table named customers has a full_name column containing first and last names separated by a space, this query returns two columns:
SELECT
full_name,
SPLIT_PART(full_name, ' ', 1) AS first_name,
SPLIT_PART(full_name, ' ', 2) AS last_name
FROM customers;
For a value such as Rina Patel, the output is Rina in first_name and Patel in last_name. The query does not change the stored table; it creates derived values in the result set. To persist the parsed values, use the query in a view or insert or update data in a table as appropriate.
The delimiter is not limited to one character. For example, SPLIT_PART(order_code, '-', 2) returns the second section of a hyphen-separated code. The delimiter is treated as a literal string, not a regular expression. That makes this function simple for predictable separators such as commas, pipes, colons, or multi-character markers.
Example: split a delimited value into named fields
Suppose a staging table has a column named record_text with values in the form 4821|Mina|Seattle|active. If the fields always appear in that order, select each position explicitly:
SELECT
SPLIT_PART(record_text, '|', 1) AS customer_id,
SPLIT_PART(record_text, '|', 2) AS customer_name,
SPLIT_PART(record_text, '|', 3) AS city,
SPLIT_PART(record_text, '|', 4) AS status
FROM staging_records;
This is a good fit when downstream users expect a stable schema: four output columns with clear names. It also makes the intended meaning visible in the SQL, which is useful when someone later reviews or changes the transformation. If a field may contain spaces, that does not matter here because the separator is a pipe.
For values that can have extra spaces around each field, wrap the extraction in TRIM, for example TRIM(SPLIT_PART(record_text, '|', 2)). Trimming helps with incidental whitespace at the edges, but it does not fix a wrong delimiter or parse a different field order.
SPLIT creates an array rather than columns
SPLIT(string, delimiter) returns an array of pieces. It does not automatically create a separate SQL column for every item. That distinction matters: a query can return the array as one value, and you can then access specific array positions or expand the array into rows.
For instance, SPLIT('red|blue|green', '|') produces an array containing the three values. Snowflake arrays use zero-based indexes when accessing array elements, so the first element is at index 0. This differs from SPLIT_PART, whose positions start at 1. Mixing up those conventions is a common source of off-by-one errors.
When the desired number of columns is known, SPLIT_PART is often easier to read than extracting array elements. Choose SPLIT when you need the array itself, want to pass it to another array operation, or plan to expand its contents. Snowflake can also cast array elements to a useful type when they are extracted; for textual output, an explicit cast to VARCHAR can make intent clear.
Use FLATTEN when each piece should become a row
If the number of pieces varies and you want one output row for each piece, use the table function FLATTEN with the array produced by SPLIT. This is a different result shape from splitting into columns: it increases the number of rows instead of creating a fixed set of fields.
SELECT
s.record_id,
f.index + 1 AS position,
f.value::VARCHAR AS item
FROM source_records AS s,
LATERAL FLATTEN(INPUT => SPLIT(s.items_text, '|')) AS f;
For a source row whose items_text is pen|notebook|folder, the query produces three rows, with positions 1, 2, and 3. The array index exposed by FLATTEN starts at zero, which is why the example adds 1 for a reader-friendly position. Keeping the source identifier in the output lets you associate each expanded value with its original record.
Use this pattern for lists of tags, product codes, or other repeated values when a row-per-item structure is more useful for filtering, joining, or aggregating. If you later need the values in separate columns again, you need a pivoting or aggregation step; FLATTEN itself does not create named columns for each possible position.
Handle missing pieces, empty values, and whitespace
Real data is often less uniform than a clean example. A row may contain fewer pieces than expected, repeated delimiters, leading or trailing spaces, or a null source value. Decide how the query should treat these cases before relying on the output.
- Missing position: If the requested part does not exist,
SPLIT_PARTreturns an empty string. You can retain that result or turn it into SQLNULLwithNULLIF. - Empty field: Two adjacent delimiters can represent an empty field, such as the middle value in
one||three. That is not necessarily the same thing as a missing field, so choose whether to preserve it. - Whitespace: Use
TRIMaround an extracted value if unwanted spaces should be removed from its edges. Do not trim blindly if spaces are meaningful data. - Null input: A null source string produces a null result. If reports should display a fallback value instead, handle that explicitly with a function such as
COALESCE.
For example, to return a null instead of an empty second field, use NULLIF(SPLIT_PART(record_text, '|', 2), ''). If whitespace should also be removed before that check, use NULLIF(TRIM(SPLIT_PART(record_text, '|', 2)), ''). This ensures a field containing only spaces is treated as empty after trimming. Test these choices against representative records, especially if empty and absent values have different meanings in the source system.
Why delimiter splitting is not always CSV parsing
A common misconception is that splitting on a comma is enough to parse any comma-separated file. It works only when commas reliably mark field boundaries. In a properly quoted CSV record, a comma inside a quoted field is data, not a separator. For example, a field containing Seattle, Washington may be enclosed in quotes; a simple split on commas cannot reliably tell that internal comma from the delimiter.
If the text comes from a CSV file, use Snowflake’s file-format and data-loading features to define how fields, quote characters, and other CSV conventions are handled. Parse the file during loading rather than treating each entire record as an ordinary string and splitting it afterward. The same caution applies to escaped quotes, embedded line breaks, and other file-format rules. SPLIT is a string function, not a general CSV parser.
For a controlled string created by your own application with a simple delimiter and no quoting rules, splitting is appropriate. If values can contain the delimiter, define an escaping or quoting convention and use a parser that understands it. Otherwise, the pieces may be silently assigned to the wrong columns.
Choosing between SPLIT_PART, SPLIT, and FLATTEN
Pick the function based on the shape you need and the consistency of the input, rather than simply choosing the function with the shortest syntax.
- Use
SPLIT_PARTfor a known set of positions that should appear as named columns in a query. - Use
SPLITwhen an array is a useful intermediate or final value. - Use
FLATTENwithSPLITwhen each piece should become its own row and the number of pieces can vary. - Use file-format parsing or a more suitable parser when the data follows CSV quoting rules or another structured format.
There is no automatic way for a relational query to create a different number of SQL columns for every row. A result set has a defined set of columns, so a variable-length list is usually represented as an array or as multiple rows. If you need a fixed number of columns but some records are shorter, select the positions you need and decide how to represent missing positions. If records can contain more pieces than the selected columns, decide whether to ignore, retain, or separately report the extras.
Frequently Asked Questions
Does Snowflake have a function that splits text into columns?
Yes. Use SPLIT_PART once for each position you want as a column. For example, separate expressions for positions 1, 2, and 3 return three columns. SPLIT returns an array instead of automatically creating columns.
Is SPLIT_PART zero-based or one-based?SPLIT_PART is one-based: the first piece is position 1. Array indexes and the index reported by FLATTEN are zero-based, so the first array element has index 0.
What does SPLIT_PART return when a field is missing?
It returns an empty string when the requested position is not present. Use NULLIF if you want an empty result to become SQL NULL, and use TRIM first if whitespace-only values should count as empty.
Can SPLIT_PART use more than one character as a delimiter?
Yes. The delimiter argument is a literal string, so it can contain multiple characters. It is not interpreted as a regular expression.
Can I split a comma-separated string when fields contain commas?
Not reliably with a plain SPLIT call. If commas inside values are quoted, use CSV-aware file-format parsing or another parser that honors the quoting rules. Simple delimiter splitting is safe only when the delimiter cannot also occur as unescaped field content.
Does splitting update the original Snowflake table?
No. A SELECT that uses SPLIT_PART returns computed columns but does not alter stored data. To save parsed values, create a view or use an appropriate table-loading or data-modification statement.
Related
- SQL Split Syntax: Split Strings by Delimiter
- Split in SQL: Practical Methods for Common Databases
- T-SQL STRING_SPLIT: Syntax, Order, 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
- How to Insert a Line Across the Page in Microsoft WordTo insert a line across the page in Word, click in a blank paragraph, then choose Home >…
- Budget Spreadsheet Free Template: Simple Excel SetupYes—you can make a free budget spreadsheet in Excel by listing categories, planned amounts, actual spending, and formulas…
- How to Animate in Microsoft PowerPoint: A Practical GuideTo animate in Microsoft PowerPoint, select text or an object, open the Animations tab, choose an effect, then…
- How to Double Space in WordTo double-space text in Microsoft Word, select the text, then choose Home > Line and Paragraph Spacing >…
- Vendor Quote Comparison Template: Build It in ExcelYes. In Excel for Microsoft 365, create one row per vendor and columns for the quoted unit price,…
- How to Make a Weekly Schedule in Google Sheets Step by StepTo make a weekly schedule in Google Sheets, create a grid with time slots down the first column…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.