SQL has no single, universal SPLIT() function: the syntax depends on your database, and some systems return pieces as rows while others return an array or one selected piece. For example, SQL Server uses STRING_SPLIT(text, delimiter), PostgreSQL commonly combines string_to_array with unnest, and BigQuery uses UNNEST(SPLIT(text, delimiter)).
Why SQL has no single SPLIT syntax
SQL is implemented by different database systems, often called dialects, and string functions are not identical across them. A query written for SQL Server may not run in PostgreSQL, MySQL, Oracle, Snowflake, or BigQuery. Even when two systems use a function named SPLIT, its result type and handling of edge cases can differ.
Before choosing syntax, clarify what you mean by splitting. You might want an array of pieces, a separate result row for each piece, or just the item at a particular position. Those are different operations. For a value such as red,green,blue, a row-producing split returns three rows; a scalar extraction might return only green; an array-producing split returns one array containing all three values.
Also distinguish splitting a simple delimited string from parsing a structured format. A comma-separated string is not necessarily valid CSV: proper CSV can include quoted fields, commas inside quotes, and escaped quotation marks. A basic SQL delimiter function generally does not understand those rules.
How splitting works
A split operation looks for a delimiter, such as a comma or pipe, and divides the input wherever that delimiter occurs. The delimiter is not usually included in the returned pieces. For example, splitting north|south|west on the pipe character produces north, south, and west.
The database may represent those pieces in different ways. A table-valued function produces rows that can be joined or filtered. An array function keeps the pieces together as one value until you expand the array. A scalar function such as PostgreSQL’s split_part returns just one position. Pick the result form that fits the next step in your query rather than assuming every split function behaves the same.
For example, if a table has a column named tags containing several tags per row, splitting into rows can make it possible to count or filter individual tags. If the goal is only to retrieve the second section of a code, a function that extracts one position may be simpler and less expensive than creating multiple rows.
SQL Server: STRING_SPLIT
In SQL Server, STRING_SPLIT returns a table of substrings. It is available in SQL Server 2016 and later when the database compatibility level is at least 130. The basic syntax is:
SELECT value
FROM STRING_SPLIT('red,green,blue', ',');
The result has a column named value, with one row for each piece. To split a column for every row in a table, use CROSS APPLY. Assuming a table named Products has a comma-delimited Tags column:
SELECT p.ProductID, s.value AS tag
FROM Products AS p
CROSS APPLY STRING_SPLIT(p.Tags, ',') AS s;
Rows returned by STRING_SPLIT are not guaranteed to be in the original string order. SQL Server 2022 and supported Azure SQL offerings can return an ordinal column when the optional third argument is enabled. Order the final result explicitly:
SELECT value, ordinal
FROM STRING_SPLIT('red,green,blue', ',', 1)
ORDER BY ordinal;
The third argument must be a constant 0 or 1. The delimiter is a single character, rather than an arbitrary multi-character string. Empty tokens can occur when delimiters are repeated, as in red,,blue; filter them if your application should ignore them. A NULL input produces an empty table.
PostgreSQL: arrays, rows, and one field
PostgreSQL offers several related choices. To produce one row per piece, turn the array returned by string_to_array into rows with unnest:
SELECT unnest(string_to_array('red,green,blue', ',')) AS part;
To get a single item by position, use split_part. Its field positions start at 1, so this returns green:
SELECT split_part('red,green,blue', ',', 2) AS part;
These functions serve different purposes: split_part does not return a table of all the pieces. For splitting on a regular-expression pattern rather than a literal delimiter, PostgreSQL also provides regexp_split_to_table. Regular expressions are useful for variable separators, but patterns need care because punctuation can have special meaning in a regular expression.
If the pieces need to stay as a collection for array operations, keep the result of string_to_array as an array. If the query needs to join each piece to another table or aggregate by each piece, expand it into rows with unnest.
MySQL: alternatives to a general split function
MySQL does not provide a general built-in function that turns an arbitrary delimited string into rows in the same way SQL Server’s STRING_SPLIT does. SUBSTRING_INDEX can extract text before or after a specified number of delimiter occurrences, but it returns a string rather than a row for every piece. It can be handy when the number and position of fields are known.
For data already stored as a valid JSON array, MySQL 8.0’s JSON_TABLE can convert array elements into rows. This example starts with JSON, not a comma-delimited string:
SELECT j.part
FROM JSON_TABLE(
'["red","green","blue"]',
'$[*]' COLUMNS (part VARCHAR(100) PATH '$')
) AS j;
Do not turn arbitrary CSV text into JSON by simply placing brackets around it or replacing commas with quotation marks. Quotes, backslashes, embedded commas, and missing values can make that conversion invalid or change the data. For simple, controlled input, a recursive common table expression is another possible approach, but it needs deliberate handling for empty values, repeated delimiters, and a trailing delimiter. For repeated use, parsing in the application or storing the values in a related table is often clearer.
BigQuery and Snowflake
BigQuery’s SPLIT returns an array. Use UNNEST to expand that array into rows:
SELECT part
FROM UNNEST(SPLIT('red,green,blue', ',')) AS part;
The result can then be filtered or joined like other rows. BigQuery also supports array operations, so it may be better to keep the result as an array when you do not need to expand it.
Snowflake also has a SPLIT function that returns an array. A common way to return its elements as rows is the table function FLATTEN:
SELECT value::STRING AS part
FROM TABLE(FLATTEN(INPUT => SPLIT('red,green,blue', ',')));
Although the names look familiar across these platforms, check the database documentation for exact return types, null behavior, and ordering guarantees before porting a query. In particular, do not rely on the displayed order of rows unless the query specifies an ordering rule.
How to choose the right method
Choose based on the database, the shape of the output, and the data’s format. A practical decision process is:
- Identify your database and version. Check whether the function exists and whether a compatibility level or newer release is required.
- Decide what the query needs. Use a scalar extraction for one known field, an array for collection operations, or rows for joins, filtering, and grouping.
- Check the delimiter rules. A literal one-character delimiter and a regular-expression pattern are not interchangeable.
- Define the edge-case behavior. Decide what empty pieces, NULL input, and a delimiter at the beginning or end should mean.
- Preserve order when it matters. Store or return a position and include an explicit
ORDER BYin the final query.
For occasional reporting on a small field, splitting at query time may be convenient. If the same data is split in many queries, or if individual values need their own constraints and indexes, store each value as a separate row in a child table instead. That avoids repeatedly parsing text and makes relationships easier to validate.
Common problems and misconceptions
Assuming SPLIT always returns rows: It may return an array, and a function such as split_part returns one scalar. Read the return type and use the database’s row-expansion feature where needed.
Expecting a stable order without asking for one: Relational query results have no guaranteed order unless an ORDER BY is used. Even if a split function exposes an ordinal, sort by it in the outer query to request the original sequence.
Ignoring empty pieces: Repeated delimiters can represent an empty field, not a missing delimiter. Some methods preserve empty pieces while others, particularly regular-expression patterns that match one or more non-delimiter characters, can skip them. Test examples such as ,red,,blue, against your chosen method.
Treating commas as a complete CSV parser: Splitting on commas breaks fields that contain quoted commas. Use a real CSV parser or an appropriate import facility when the input follows CSV rules. A SQL split function is for delimiter-separated text with simpler, known rules.
Splitting data that should be relational: A list stored in one column can make searches, validation, and joins harder. If each item has meaning as an independent value, a separate table is often a stronger data model than repeatedly splitting strings.
Frequently Asked Questions
Is there a standard SPLIT function in SQL?
No single general-purpose SPLIT function works the same way across SQL databases. The available function, syntax, and result type depend on the database. Use the documentation for the specific engine and version.
How do I split a string into rows in SQL Server?
Use STRING_SPLIT(text, delimiter) in the FROM clause. To apply it to a column for each source row, use CROSS APPLY. Add an explicit ordering step if the sequence matters.
What is the difference between SPLIT and split_part?
A split function may return an array or multiple rows, depending on the database. PostgreSQL’s split_part returns just one field at a requested, 1-based position; it does not return every piece as rows.
How can I keep the original order after splitting?
Use a function or method that provides a position for each piece, then sort by that position. SQL Server 2022 supports an ordinal output from STRING_SPLIT when enabled. In other systems, use an array index or another explicit position where available.
Can a SQL split function parse CSV?
Usually not. Splitting on commas alone does not account for quoted fields, escaped quotes, or commas inside a field. Use a CSV-aware parser or data-loading tool for actual CSV input.
Related
- Split a String in Excel: Separate Text into Columns
- Accounting Trial Balance Worksheet: How It Works
- Google Sheets SPLIT Function: How to Separate Text
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel LEFT Function: How to Extract Text from the StartTo return characters from the beginning of text in Excel, enter =LEFT(A2,3) in a cell, replacing A2 with…
- Calculate Hours in Excel: Formulas and Time FormatsTo calculate elapsed hours in Excel, put the start time in A2 and end time in B2, then…
- Excel Formula to Pick Randomly From a ListWith a contiguous list in A2:A10, use =INDEX(A2:A10,RANDBETWEEN(1,ROWS(A2:A10))) to return one randomly selected item. The result can change…
- How to Highlight in Excel Using a FormulaTo highlight cells or entire rows in Excel based on a condition, select the range, then choose Home…
- XLOOKUP Example With SUM in ExcelWith product names in A2:A5, monthly values in B2:D5, and the product to find in E2, use =SUM(XLOOKUP(E2,A2:A5,B2:D5)).…
- Concatenate 2 Dates in Excel: Formulas and FormattingTo concatenate two dates in Excel, convert each date to readable text with TEXT, then join the results…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.