SQL String Split Function: Syntax by Database Engine

Formulas & functions · Updated November 22, 2026

There is no single SQL string-split function that works in every database: SQL Server uses STRING_SPLIT, PostgreSQL commonly combines string_to_array with unnest, and BigQuery provides SPLIT. The right syntax depends on your database, and you should explicitly handle ordering, empty values, and nulls rather than assuming a split returns tokens in a particular order.

What a SQL string-split function does

A string-split operation takes one text value containing a delimiter-separated sequence and returns the individual pieces, often called tokens. For example, splitting 'red,green,blue' on a comma produces three values: red, green, and blue. Depending on the database, those values may be returned as rows, an array, or a table-like result.

This matters because a delimited string is not the same thing as a set of relational values. If an application stores several tags in one column, a split function can help query or migrate that data, but it does not change the underlying design. When the values need to be searched, updated, or joined regularly, storing them in separate related rows is usually easier to maintain.

SQL Server: STRING_SPLIT

In SQL Server, the built-in table-valued function is STRING_SPLIT. It is available in SQL Server 2016 and later when the database compatibility level is at least 130. The basic call returns a column named value:

SELECT value
FROM STRING_SPLIT('red,green,blue', ',');

The delimiter argument must be a single character. The result is a table, so you can use it in a FROM clause and combine it with other tables. For example, if Products has a comma-separated Tags column, this query returns one row per tag and product:

SELECT p.ProductID, s.value AS Tag
FROM dbo.Products AS p
CROSS APPLY STRING_SPLIT(p.Tags, ',') AS s;

SQL Server 2022 adds an optional third argument that enables an ordinal column. Set it to the constant 1 when you need each token’s original position:

SELECT ordinal, value
FROM STRING_SPLIT('red,green,blue', ',', 1)
ORDER BY ordinal;

The ordinal identifies position, but the function still does not promise that rows arrive in that order unless the query includes ORDER BY. The ordinal option is available in SQL Server 2022 and supported Azure SQL offerings; check the documentation for your specific service. On earlier SQL Server versions, use another method if the original token order is essential.

PostgreSQL: split into an array or rows

PostgreSQL’s string_to_array turns delimited text into an array. Use unnest to expand that array into one row per element:

SELECT unnest(string_to_array('red,green,blue', ',')) AS value;

To retain positions, add WITH ORDINALITY. PostgreSQL ordinality starts at 1, making it convenient when you want human-readable positions:

SELECT u.value, u.ordinality
FROM unnest(string_to_array('red,green,blue', ','))
     WITH ORDINALITY AS u(value, ordinality)
ORDER BY u.ordinality;

PostgreSQL also offers regexp_split_to_array and regexp_split_to_table when the separator is a regular expression rather than a fixed delimiter. Regular expressions are useful for variable separators, such as one or more spaces, but they can be harder to read and may do more work than a simple delimiter split. Choose them only when the input format calls for pattern matching.

MySQL: no general SPLIT function

MySQL does not provide a general-purpose built-in function named STRING_SPLIT that returns every token as a row. SUBSTRING_INDEX can extract text before or after a delimiter, so it is useful when you need a known position, such as the first component of a value. It is not a direct replacement for returning an arbitrary number of tokens as rows.

For values already stored as JSON arrays, MySQL’s JSON_TABLE can turn array elements into rows. For example, with this JSON array literal, the query returns each value and its one-based position:

SELECT j.position, j.value
FROM JSON_TABLE(
  '["red","green","blue"]',
  '$[*]' COLUMNS (
    position FOR ORDINALITY,
    value VARCHAR(100) PATH '$'
  )
) AS j;

Do not turn arbitrary comma-separated text into JSON by simply adding brackets and quotes. A token may contain quotation marks, backslashes, or other characters that need correct JSON escaping, and a comma inside a token may not be a separator at all. If the source is ordinary delimited text, use a carefully designed stored routine or application-side parser, or normalize the data into rows.

BigQuery, Snowflake, and other SQL dialects

Some cloud data warehouses provide concise split functions but return arrays rather than rows. In BigQuery, SPLIT creates an array and UNNEST expands it. To include a zero-based position, use WITH OFFSET:

SELECT token, position
FROM UNNEST(SPLIT('red,green,blue', ',')) AS token
WITH OFFSET AS position
ORDER BY position;

Snowflake provides SPLIT, which returns an array, and FLATTEN, which produces rows from an array. Its output includes an index that can be used to recover the token position; order the final query explicitly if sequence matters. Oracle has options such as REGEXP_SUBSTR for extracting pieces and APEX_STRING.SPLIT in environments that include the relevant APEX package. SQLite has no built-in general-purpose split-to-rows function, so a recursive common table expression or application code is often used.

These differences are why a query copied between database products may fail even when the idea is the same. Check the database engine and version, as well as whether the function returns an array or a rowset, before choosing syntax.

Ordering, empty tokens, and null values

Splitting seems simple until the input contains unusual cases. Consider 'red,,blue,'. The doubled comma represents an empty piece between delimiters, and the final comma may represent an empty piece at the end. Some functions preserve empty pieces, while other approaches or later filtering steps may remove them. Test the behavior your database actually uses before relying on a particular token count.

  • Ordering: A rowset has no guaranteed display order without ORDER BY. Use an ordinal column when available, then sort by it.
  • Whitespace: Splitting on a comma does not necessarily remove spaces. A token may be ' green', not 'green'. Apply the database’s trim function when whitespace should be discarded.
  • Null input: Null handling varies by function. SQL Server’s STRING_SPLIT returns an empty table for a null input string. Other expressions may propagate null or produce an empty array.
  • Empty input: An empty string is not always treated like null. Check whether it yields an empty token, no rows, or an empty array in your engine.
  • Delimiter rules: SQL Server’s built-in delimiter is one character. Other functions may accept strings or patterns, but a regular-expression delimiter has different rules from a literal comma.

One frequent misconception is that the order of rows returned by a split function will match the original text. Even if it appears to do so in a small test, SQL query results are unordered unless you specify sorting. Another is that trimming the entire input also trims every token: trimming ' red, green ' only removes whitespace at the outside edges, not necessarily the spaces beside the internal comma.

Using split results in a query

A split result is useful for filtering, joining, and counting. In SQL Server, for instance, you can filter a product’s tags by splitting its value and checking for a requested tag. Be careful to compare complete tokens: searching the original string with a pattern such as %red% could also match a different word containing those letters. With tokens in rows, an equality comparison is clearer.

When splitting a column for many source rows, use the database’s table-valued or lateral mechanisms correctly. SQL Server uses CROSS APPLY for a function evaluated against each row. PostgreSQL supports set-returning functions in the FROM clause, and BigQuery uses UNNEST. If the input is null, an inner-style expansion may leave no output row for that source record. If you need to retain the source record even when there are no tokens, investigate the appropriate outer join or apply form for your database.

Also consider duplicate tokens. Splitting 'red,red,blue' normally yields both occurrences of red; it does not automatically make the values unique. Use DISTINCT only if duplicates should be ignored, since removing them can change the meaning of the data.

How to choose a splitting approach

Start with the database’s native function when it matches the input and output you need. A built-in function is usually easier to maintain than custom parsing, and it can expose useful position information. Before using it in production, answer these practical questions:

  • Which engine and version runs the query, and is any compatibility setting required?
  • Is the delimiter always one literal character, a longer string, or a pattern?
  • Do empty pieces and repeated values need to be preserved?
  • Do you need original positions, and can the query sort by an ordinal?
  • Can tokens contain the delimiter, quotes, or escaped characters?
  • Will this parse a one-time import or run repeatedly against a large table?

If delimiters can appear inside values, a plain split cannot reliably tell whether a comma is structural or part of a token. Use a format with explicit quoting and escaping, such as correctly parsed CSV, or use structured storage such as a JSON array when appropriate. For frequent joins and filters, a child table with one value per row is generally more dependable than parsing the same text on every query.

Frequently Asked Questions

What is the SQL Server string split function?

SQL Server uses STRING_SPLIT(string, separator) to return separated values in a table with a value column. It requires SQL Server 2016 or later and database compatibility level 130 or higher.

How do I keep the original order of split values?

Use an ordinal or position column when the database provides one, then add ORDER BY on that column. SQL Server 2022 supports the optional ordinal argument; PostgreSQL offers WITH ORDINALITY, and BigQuery offers WITH OFFSET.

Does MySQL have STRING_SPLIT?

No general-purpose MySQL STRING_SPLIT function returns every delimited token as a row. SUBSTRING_INDEX can extract a known section, while JSON_TABLE can expand a JSON array into rows.

How can I split a string by a space or comma?

Pass the delimiter required by your database’s function: a comma for comma-separated input or a space for space-separated input. If spacing is inconsistent, use a suitable pattern-based function where available, or trim each resulting token.

Should I store comma-separated values in a SQL column?

Usually not when individual values need regular searching, updating, or joining. A separate related row for each value makes those operations easier and avoids repeatedly parsing text. A delimited field can still be practical for a transient import or a value treated as an opaque string.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

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

Spotted an error in this guide? Let us know.