SQL Split Syntax: Split Strings by Delimiter

How-to basics · Updated November 22, 2026

SQL has no single, universal SPLIT() syntax: the right method depends on your database. For example, SQL Server uses STRING_SPLIT, PostgreSQL offers string_to_array and string_to_table, and BigQuery uses SPLIT with UNNEST. Check your database’s dialect before using an example.

What splitting a string means in SQL

Splitting means dividing one text value into smaller pieces at a chosen delimiter, such as a comma, pipe, or space. Given the value red,green,blue and a comma delimiter, a split produces three values: red, green, and blue.

The result may be a set of rows, an array, or just one requested piece. That distinction matters. A row-producing function is useful when you need to filter, join, or count each item separately. An array can be convenient for further array operations. A function that returns one piece is handy when you know which position you want.

Splitting is different from extracting text before or after a delimiter. It is also not necessarily the right way to store repeated data. If a value represents several independently searchable items, storing each item in its own row in a related table is usually easier to query and validate.

Why SQL split syntax varies

SQL is implemented in different database products, each with its own functions and supported versions. A query written for one product may fail in another even when the databases both support standard SQL. There is no portable built-in function named SPLIT that behaves identically everywhere.

Before choosing syntax, identify the database and version: for instance, SQL Server, PostgreSQL, MySQL, Oracle Database, BigQuery, or SQLite. Also decide what the output needs to be. Do you need all pieces as separate rows, an array, or only the second item? Finally, check how the function handles empty pieces, NULL inputs, and result order; those details can affect correctness.

SQL Server: STRING_SPLIT

In SQL Server, STRING_SPLIT returns a table containing one row for each substring. It is available in SQL Server 2016 and later, and the database compatibility level must be at least 130. For the input below, the query returns three rows, though you should not assume they appear in the original order.

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

Use the returned table like other table sources. For example, with a table named Products and a comma-separated Tags column, CROSS APPLY STRING_SPLIT(Products.Tags, ',') can expose each tag as a row alongside its product. Trim whitespace if the stored values include spaces after commas; splitting at a comma does not automatically remove surrounding spaces.

SQL Server 2022 and supported Azure SQL offerings add an optional third argument that enables an ordinal column. The ordinal identifies each piece’s position, but ordering still requires an explicit ORDER BY:

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

The third argument must be a constant bit or integer value of 0 or 1. Do not use this form unless the SQL Server product and version support it. The separator for STRING_SPLIT is a single character, so a multi-character delimiter may need preprocessing or a different approach.

PostgreSQL: arrays, rows, and positions

PostgreSQL provides several useful choices. string_to_array turns a string into an array; unnest expands that array into rows. This query returns the three color values as rows:

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

If you want a set-returning function that directly produces rows, use string_to_table:

SELECT value
FROM string_to_table('red,green,blue', ',') AS value;

When you need only one piece, split_part returns the requested field, using a one-based position. For example, split_part('red,green,blue', ',', 2) returns green. PostgreSQL also supports delimiters made of more than one character in these functions, unlike SQL Server’s single-character STRING_SPLIT separator.

As with any set of rows, do not rely on an implicit output order if order is important to later processing. Generate or retain a position and sort by it explicitly. Also consider whether an empty delimiter or a NULL argument has the behavior your application expects.

MySQL: extracting pieces with SUBSTRING_INDEX

MySQL does not provide a built-in table-valued SPLIT function equivalent to SQL Server’s STRING_SPLIT. Its SUBSTRING_INDEX function returns text on one side of a specified number of delimiter occurrences. For example, SUBSTRING_INDEX('red,green,blue', ',', 2) returns red,green, the text before the second comma.

To extract the second item from this simple comma-separated value, nest the function: first take the text through the second delimiter, then take the part after the last delimiter in that result.

SELECT SUBSTRING_INDEX(
         SUBSTRING_INDEX('red,green,blue', ',', 2),
         ',', -1
       ) AS value;

This returns green. The approach is practical when the position is known, but it does not by itself turn every item into a row. For row-based processing, the appropriate solution depends on the MySQL version and the data format; in MySQL 8.0, for example, JSON functions such as JSON_TABLE can turn a JSON array into rows. If the source is ordinary delimited text, converting it safely to JSON requires care, especially when values can contain quotes, backslashes, or the delimiter.

BigQuery, Oracle, and SQLite alternatives

BigQuery has a SPLIT function that returns an array. Use UNNEST to produce one row per item. To preserve position, include the offset and sort by it; offsets begin at zero.

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

Oracle Database can extract delimited values with regular-expression functions such as REGEXP_SUBSTR, often combined with a hierarchical query. This is more involved than a dedicated split function, and a pattern that skips empty fields may not preserve them. Test the expression against the exact input cases your application permits.

SQLite has no built-in general-purpose string-splitting function. A recursive common table expression can repeatedly locate a delimiter and return pieces, but the query is more complex than a one-function solution. If the values are under your control, storing them as separate rows or using a structured format supported by your SQLite environment may be simpler. Do not assume an extension or JSON feature exists without checking the SQLite build and version in use.

How to choose the right approach

Pick a method based on both the database and the work you need to do with the result. A function that returns one selected field is not a replacement for a row-producing split when you need to join every item to another table.

  • Need every piece as a row: use a table-valued function such as SQL Server’s STRING_SPLIT, PostgreSQL’s string_to_table, or BigQuery’s UNNEST(SPLIT(...)).
  • Need one known position: consider PostgreSQL’s split_part or, in MySQL, an appropriately nested SUBSTRING_INDEX.
  • Need the original order: use an ordinal or offset if the database provides one, then sort explicitly. Do not treat an apparent result order as a guarantee.
  • Have a multi-character delimiter: check the function’s delimiter rules. PostgreSQL and BigQuery support delimiter strings, while SQL Server’s STRING_SPLIT takes a single-character separator.
  • Can change the schema: consider storing one item per row instead of repeatedly splitting a delimited column at query time.

For a one-off report, splitting at query time may be entirely reasonable. For a frequently used operation, compare its clarity and performance with a normalized design, and inspect the execution plan on representative data. There is no universal performance winner: row counts, indexes, data distribution, and the database engine all matter.

Common problems and misconceptions

Assuming the output is ordered is a frequent mistake. A database returns rows in a guaranteed order only when the query specifies an ORDER BY. If the splitting function supplies an ordinal, sort by that value; if it does not, you may need another design to preserve positions.

Forgetting spaces can leave values such as green rather than green. Apply the database’s trimming function where appropriate, and consider whether whitespace is meaningful in the data. Splitting and trimming are separate operations.

Confusing delimited text with CSV can cause data loss. A simple split on commas cannot correctly parse every CSV file: quoted fields may contain commas, quotation marks, or line breaks. Use a proper CSV parser when the source follows CSV rules rather than treating it as plain comma-separated text.

Overlooking empty values can also change the meaning of a result. For example, red,,blue has an empty item between two delimiters, while a trailing delimiter may represent another empty item. Database functions differ in how they represent or handle such cases. Test consecutive delimiters, leading and trailing delimiters, empty strings, and NULL values before relying on a split result.

Finally, splitting a list stored in one database column does not make that column relationally searchable in the same way as a proper child table. If users need to filter, update, or enforce constraints on individual items, a separate table with one row per item is usually a more maintainable model.

Frequently Asked Questions

Is there a universal SQL SPLIT function?
No. Function names, return types, delimiter limits, and ordering behavior vary by database. Use the syntax documented for the database engine and version running your query.

How do I split a comma-separated string into rows in SQL Server?
Use STRING_SPLIT(your_column, ',') in the FROM clause, provided the SQL Server version and database compatibility level support it. Add an explicit ordering strategy if the sequence matters.

How do I get the second value after splitting?
In PostgreSQL, use split_part(your_text, ',', 2). Other databases have different methods; in MySQL, nested SUBSTRING_INDEX can extract a known position from simple delimited text.

Does STRING_SPLIT preserve the original order?
Do not rely on its output order. SQL Server 2022 and supported Azure SQL offerings can return an ordinal when the optional argument is enabled; use ORDER BY ordinal to request that order.

Can I split CSV data with a SQL delimiter function?
Not reliably when fields may be quoted or contain commas, quotes, or line breaks. Use a CSV-aware parser or import tool for actual CSV, rather than a basic string-splitting function.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

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

Spotted an error in this guide? Let us know.