There is no single, standard SQL SPLIT() function that works in every database. To split text into rows, use your database’s equivalent: SQL Server has STRING_SPLIT, PostgreSQL has string_to_array with unnest, and Snowflake and BigQuery have their own split-and-expand patterns.
What a SQL split function does
A split operation takes one text value, finds a delimiter such as a comma or pipe, and separates the text into pieces. Depending on the database and function, those pieces may be returned as an array, a table of rows, or one selected piece. For example, splitting red,green,blue on a comma produces three values: red, green, and blue.
This is useful when a column contains multiple values in a delimited string, when importing a text file, or when a query needs to compare individual tokens. It is not a universal SQL feature: the function name, result shape, delimiter rules, and support for retaining positions vary between database systems and sometimes between versions of the same system.
Splitting is often a practical way to handle incoming or legacy data, but storing several independent values in one column can make searching, validation, and updates harder. For data you control, a related table with one value per row is usually easier to query reliably.
How to choose the right syntax
First identify the database engine and version. A query written for SQL Server will not automatically run in PostgreSQL or MySQL, even when the goal is identical. Then decide what result you need: a list of rows, an array, a particular item, or each item alongside its original position.
- Need rows in SQL Server? Start with
STRING_SPLIT. - Need rows in PostgreSQL? Convert the string to an array with
string_to_array, then expand it withunnest. - Using Snowflake or BigQuery? Split into an array and expand it with the platform’s table or array function.
- Using MySQL or SQLite? Check the version and available JSON features; neither offers a broadly equivalent built-in string-to-rows function with the same syntax as SQL Server.
Also check whether your delimiter is one character or several, whether repeated delimiters should create empty values, and whether output order matters. Those details can change the right solution.
SQL Server: STRING_SPLIT examples
In SQL Server, STRING_SPLIT turns a delimited string into a table with a value column. With a string in a query, a basic example is:
SELECT value
FROM STRING_SPLIT('red,green,blue', ',');
The result contains one row per token. SQL Server does not guarantee the order of those rows just because the tokens appeared in a particular order in the input. If order matters, use the ordinal option where supported and explicitly sort the result.
SQL Server 2022 and supported Azure SQL services allow a third argument to request an ordinal column. With the ordinal option enabled, the function returns each value with its position:
SELECT value, ordinal
FROM STRING_SPLIT('red,green,blue', ',', 1)
ORDER BY ordinal;
The third argument must be a constant value of 0 or 1. The ordinal option is available in SQL Server 2022 and supported Azure SQL products; do not assume it exists on older SQL Server versions. The function itself requires database compatibility level 130 or higher. Its separator is a single character, so a multi-character delimiter needs another approach.
For example, if a table has a column called Tags containing comma-separated text, you can apply STRING_SPLIT to each row with CROSS APPLY. The precise query should also account for nulls, whitespace, duplicate tokens, and whether an empty token is meaningful in your data.
PostgreSQL: turn a string into rows
PostgreSQL’s string_to_array makes an array, while unnest expands array elements into rows. Together, they provide a common split-to-rows pattern:
SELECT value
FROM unnest(string_to_array('red,green,blue', ',')) AS t(value);
To keep each token’s position, use WITH ORDINALITY and sort by the generated position. This is helpful when the original sequence matters, such as when parsing an ordered list of steps.
SELECT value, ordinal
FROM unnest(string_to_array('red,green,blue', ','))
WITH ORDINALITY AS t(value, ordinal)
ORDER BY ordinal;
Unlike SQL Server’s single-character separator, PostgreSQL’s string_to_array accepts a text delimiter, including a delimiter made of multiple characters. PostgreSQL also has regular-expression splitting functions, such as regexp_split_to_table, for cases where separators follow a pattern rather than being one fixed string. Regular expressions are more flexible, but can be harder to read and may need more care with special characters.
MySQL: what to use instead
MySQL does not provide a direct built-in SPLIT function that returns every part of a delimited string as rows in the same way as SQL Server’s STRING_SPLIT. SUBSTRING_INDEX can extract a piece when you know which position you want, but it does not by itself turn every token into a result row.
For instance, this expression extracts the second comma-separated item from a string:
SELECT SUBSTRING_INDEX(
SUBSTRING_INDEX('red,green,blue', ',', 2),
',', -1
) AS second_item;
It returns green. That nested expression is useful for a known position, but repeatedly writing it for an unknown number of items is not a general-purpose splitting solution. For JSON arrays, MySQL 8.0 provides JSON_TABLE, which can turn JSON elements into rows:
SELECT jt.position, jt.value
FROM JSON_TABLE(
'["red","green","blue"]',
'$[*]' COLUMNS (
position FOR ORDINALITY,
value VARCHAR(100) PATH '$'
)
) AS jt;
This example expects valid JSON, not ordinary comma-separated text. If your input is CSV, do not treat it as JSON unless it has been correctly encoded as a JSON array. For recurring workloads, parsing before data reaches the database or normalizing the values into a related table may be clearer and safer than maintaining a custom splitting query.
Snowflake, BigQuery, and SQLite
Cloud databases may use an array as an intermediate result and then expand it. In Snowflake, SPLIT creates an array and FLATTEN exposes its elements as rows. For example:
SELECT value::STRING
FROM TABLE(FLATTEN(INPUT => SPLIT('red,green,blue', ',')));
In BigQuery, SPLIT returns an array, and UNNEST expands the array into rows:
SELECT item
FROM UNNEST(SPLIT('red,green,blue', ',')) AS item;
SQLite has no built-in general-purpose string-to-rows split function matching these examples. Its JSON functions can expand a JSON array with json_each, when JSON support is available, but a comma-separated string is not automatically a JSON array. For plain delimited input, options include parsing in the application, using a carefully designed recursive query, or changing the data format. Choose based on the SQLite version and how the application handles JSON.
Order, empty values, and other edge cases
A split query can look correct on simple input and still behave unexpectedly on real data. Test the cases that reflect how the strings are created, not just a tidy example with one delimiter between every value.
- Row order: Relational query results have no guaranteed order without an
ORDER BY. If the database provides an ordinal, sort by it when reproducing the original sequence. - Repeated delimiters: Input such as
red,,bluemay produce an empty token between the commas. Whether empty values are kept depends on the function. Decide whether to preserve, filter, or reject them. - Whitespace: A token such as
greenmay include a leading space. Trimming can help, but only trim whitespace if it is not meaningful in the data. - Null and empty input: A null string is different from an empty string. Check how the chosen database function handles each, and decide whether the expected result is no rows, an empty token, or a null value.
- Delimiter inside a value: Splitting on commas is not enough to parse full CSV reliably. CSV allows quoted fields that can contain commas, quotes, and line breaks. Use a CSV-aware parser for CSV files rather than a simple delimiter split.
- Duplicate values: Splitting does not remove duplicates. If the requirement is unique tokens, apply an explicit duplicate-handling step and consider whether order should be retained.
Splitting versus normalizing the data
Splitting a string inside a query is often appropriate at an import boundary or when working with an existing format. It can also help with a one-off report. However, a column such as Tags = 'red,green,blue' stores several values in one field. Searching for one exact tag, enforcing a foreign key, updating a single tag, and counting relationships all become more complicated.
When the data model is under your control, a separate child table is usually a better long-term design. Store one tag per row, linked to the parent record by an ID. That makes each value independently searchable and allows the database to apply constraints and indexes to individual values. Splitting is still useful for converting older data into that structure, but it does not replace deciding how the data should be stored.
Finally, treat split values as data, not as fragments of executable SQL. Do not build a query by concatenating untrusted tokens into SQL text. Use parameterized queries for values and validate inputs according to the application’s requirements. A split function separates text; it does not sanitize input or prevent SQL injection.
Frequently Asked Questions
Is SPLIT a standard SQL function?
No single standard SPLIT() function works across all SQL databases. Each engine has its own functions and syntax, and some, such as MySQL and SQLite, need a different approach for turning plain delimited text into rows.
How do I split a comma-separated string into rows in SQL Server?
Use STRING_SPLIT(your_string, ',') in the FROM clause. SQL Server 2022 and supported Azure SQL services can also return an ordinal when you pass the third argument as 1; use ORDER BY ordinal when sequence matters.
Does STRING_SPLIT preserve the original order?
Do not rely on its output order. Request ordinals on a version that supports them, then explicitly order by the ordinal column. Without an ordinal, the database does not promise that rows will appear in input sequence.
Can SQL split a string using a multi-character delimiter?
It depends on the database. PostgreSQL’s string_to_array accepts a text delimiter, while SQL Server’s STRING_SPLIT uses a single-character separator. Check the function documentation for your engine before choosing a pattern.
Is splitting a CSV string on commas reliable?
Not for general CSV data. A comma inside a quoted field is part of the value, not a separator, and CSV also has rules for quotes and line breaks. Use a CSV-aware import or parsing tool when those cases are possible.
Related
- Encrypt an Excel Sheet: Passwords, Limits, and Safe Sharing
- Encrypt and Password-Protect an Excel File
- Encrypt Cells in Excel? What You Can Protect Instead
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- INDIRECT Excel Formula for a Different Sheet: ExamplesIf cell A1 contains a worksheet name and you want the value in B2 on that sheet, use…
- DATEDIF in Excel: Calculate Days Between Dates CorrectlyTo get the number of days between a start date in A2 and an end date in B2,…
- Excel COUNTIFS Multiple Criteria Match?Yes, Excel’s COUNTIFS function can match multiple criteria across different columns (or the same column) using AND logic—all…
- Excel SUMIF Less Than: Formula, Examples, and TipsIf the values to test are in A2:A6 and the amounts to add are in B2:B6, use =SUMIF(A2:A6,"<100",B2:B6)…
- Excel Sequence Month Dates: Formula ExamplesTo create a sequence of monthly dates in Excel for Microsoft 365, enter a starting date in A1,…
- SQL SPLIT Function Syntax and Examples by DatabaseSQL has no single, universal SPLIT() function: the syntax depends on your database, and some systems return pieces…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.