SQL Split String by Delimiter: Functions by Database

Formulas & functions · Updated November 22, 2026

There is no single SQL function for splitting a string by a delimiter across all databases. Use STRING_SPLIT in SQL Server, string_to_array with unnest in PostgreSQL, and a database-specific approach in MySQL, Oracle, or SQLite.

Why the answer depends on your database

SQL is implemented by different database systems, and string functions are not fully standardized across them. A query that works in SQL Server may fail in PostgreSQL because the function names, return types, and rules for ordering or empty values differ. Before choosing a solution, identify both the database product and its version. A cloud service may also support features that an older on-premises release does not.

It also matters what you mean by “split.” A function might return one row per piece, an array of pieces in one value, or just the piece at a requested position. Those outputs suit different tasks. Returning rows is useful when each token must be filtered or joined; an array can be convenient for further processing within a query; extracting a fixed-position part may be enough for a quick report.

SQL Server: STRING_SPLIT

In SQL Server, STRING_SPLIT turns a delimited string into rows. It is available in SQL Server 2016 and later when the database compatibility level is at least 130. The separator is a single character, not an arbitrary multi-character string.

For example, this returns one value for each color:

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

The output contains the values red, green, and blue, but the function does not guarantee their order. If sequence matters, SQL Server 2022 and supported Azure SQL offerings provide an optional third argument that enables an ordinal column:

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

Here, ordinal identifies each token’s position, beginning at 1. The ORDER BY is still important: relational query results have no guaranteed order unless the query explicitly sorts them. On versions without the ordinal option, do not assume that the rows will appear in the original string’s sequence.

Consecutive delimiters can produce empty tokens, so a value such as 'red,,blue' can include an empty string between red and blue. A NULL input produces an empty table. If empty pieces are not useful, filter them deliberately, for example with WHERE value <> ''. Do not confuse an empty token with NULL; they are different values and may need different handling.

PostgreSQL: split into an array or into rows

PostgreSQL’s string_to_array splits text into an array. To turn the array into a row set, pass it to unnest. To retain each token’s position, use WITH ORDINALITY:

SELECT token, position FROM unnest(string_to_array('red,green,blue', ',')) WITH ORDINALITY AS parts(token, position) ORDER BY position;

This returns three rows with positions 1, 2, and 3. The array-only form is useful when the rest of the query can work with an array; the row form is usually easier to join or aggregate. PostgreSQL also offers regexp_split_to_table when the separator needs to be a regular expression rather than a literal delimiter. That flexibility is useful for patterns such as runs of whitespace, but regular expressions have their own syntax and can be less straightforward than a simple delimiter.

Choose between a literal separator and a regular expression carefully. A period, for example, has special meaning in many regular-expression patterns. If you intend to split on a literal punctuation character, use the literal-delimiter function or escape the character correctly in the regular expression.

MySQL: alternatives to a built-in split function

MySQL does not provide a general-purpose built-in function that directly returns every piece of an arbitrary delimited string as rows. SUBSTRING_INDEX can retrieve text before or after a specified occurrence of a delimiter, which works for a known position. It is less convenient when the number of pieces varies and every token is needed.

For repeated splitting, common approaches include a recursive common table expression, a numbers table, or converting suitably formatted input to JSON and reading it with JSON_TABLE in MySQL 8.0.4 and later. A recursive CTE can repeatedly locate the next delimiter and return the section before it, while carrying the remaining text into the next iteration. This is more code than SQL Server’s one-function solution, so test how it handles a missing delimiter, an empty input, and consecutive delimiters.

JSON conversion is not automatically safe for arbitrary text. Replacing commas with JSON separators will break if the data contains quotes, backslashes, line breaks, or values that need JSON escaping. It also assumes that the delimiter is the one being converted. Use JSON methods only when you can produce valid JSON reliably, or when the stored data is already JSON. For recurring work, a normalized table or a carefully designed stored routine may be clearer than repeatedly parsing a string.

Oracle and SQLite approaches

Oracle commonly uses REGEXP_SUBSTR with a hierarchical query to extract successive matches. A pattern such as [^,]+ finds runs of characters that are not commas. That is concise for simple comma-separated values, but it skips empty fields between consecutive commas. If empty tokens or exact positions matter, adapt the pattern and query rather than assuming the simple expression preserves them. Oracle installations may also have additional packages or application-specific utilities, so check what is available in the environment you are targeting.

SQLite does not include a general built-in function that splits arbitrary text into rows. A recursive common table expression can locate each delimiter and peel off tokens in sequence. SQLite’s JSON functions can also help when the input is valid JSON, but a plain delimited string is not JSON. As with MySQL, converting text by simple replacement is unsafe if the data can contain characters that need escaping.

These differences are a reason to avoid copying a query from an unrelated database example without checking its dialect. A method described as “SQL split” may depend on a particular vendor function, an extension, or a version-specific feature.

How to choose the right method

Start with the shape of the result you need, then account for the data format and database support. These questions help narrow the choice:

  • Do you need rows or one array? Use a row-producing method when tokens must be joined, filtered, grouped, or returned individually.
  • Must the original order be preserved? Choose a method that provides a position number, and sort by that number in the final query.
  • Is the delimiter literal or a pattern? Use a literal splitter for a fixed character or string; use a regular-expression method only when the separator is genuinely a pattern.
  • Can values contain the delimiter? If so, a plain split cannot tell whether that character separates fields or belongs inside one. Use a format with quoting rules or store the values as separate fields.
  • Does the method exist in your version? Check compatibility level and product version, especially for SQL Server’s ordinal option and MySQL’s JSON features.

For a one-off query over simple input, a built-in function or compact CTE may be adequate. If the same parsing logic appears throughout an application, consider moving the transformation to a controlled ingestion step or changing how the data is stored. Repeatedly splitting a column in filters can make queries harder to read and may prevent efficient use of indexes on the original text.

Common edge cases and data-quality problems

Splitting is not the same as parsing a full file format. A comma-separated value that permits quoted commas—such as "North, East"—cannot be reliably handled by simply splitting at every comma. CSV has rules for quotes and escaped quotes; use a CSV-aware parser when processing CSV rather than a basic SQL string function.

Also decide what should happen with whitespace and missing values. Splitting 'red, green' may return a second token with a leading space. Some databases or query patterns trim tokens automatically only if you explicitly ask them to; do not assume that they do. Likewise, 'red,,blue', ',red', and 'red,' test whether a method preserves leading, internal, and trailing empty fields. Check these cases against the chosen database before relying on the result.

Finally, splitting can conceal a data-model issue. If a table stores several independent values in one column, searching or joining those values becomes more complicated. When the values are meaningful entities that need separate updates, constraints, or relationships, a child table with one value per row is usually a better relational design than a permanently delimited field.

Performance and safer query design

Splitting a short input string once is usually a different workload from splitting a large text column for every row in a large table. In the latter case, parsing adds work for each row and can complicate filtering. If the same tokens are frequently queried, storing them as rows or maintaining a normalized representation can make the data easier to index and use.

Pass user-supplied text as a query parameter rather than assembling SQL by concatenating it into a query string. A delimiter is data too; parameterizing it where the database interface allows helps keep values separate from SQL syntax. Parameterization does not fix a bad parsing format, but it reduces the risk of treating user input as executable SQL. For production queries, test realistic input sizes and edge cases, and inspect the query plan when performance is important.

Frequently Asked Questions

What is the SQL function to split a string by a delimiter?
There is no universal function. SQL Server uses STRING_SPLIT; PostgreSQL can use string_to_array with unnest; other databases use their own functions or query patterns.

Does STRING_SPLIT preserve the order of values?
Not by default. In SQL Server 2022 and supported Azure SQL services, enable the ordinal output and sort by it. In any database, use an explicit ordering column and ORDER BY when sequence matters.

How do I split a string into rows in PostgreSQL?
Use unnest(string_to_array(text_value, delimiter)). Add WITH ORDINALITY if you need token positions, and order the final result by that position.

Can SQL split a comma-separated list with quoted commas?
A basic delimiter function cannot correctly interpret CSV quoting rules. Use a CSV parser or load the data with a CSV-aware tool, then store individual fields separately.

Can I split on more than one character?
It depends on the database and method. PostgreSQL’s string-to-array function accepts a text delimiter, while SQL Server’s STRING_SPLIT accepts a single-character separator. Regular-expression methods can match more complex patterns, but their syntax and behavior vary by database.

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.