SQL String Split: How to Separate Delimited Values

How-to basics · Updated November 22, 2026

To split a delimited string into rows, use the function supported by your database: SQL Server has STRING_SPLIT, while PostgreSQL commonly uses string_to_array with unnest. SQL syntax differs by database, and there is no single split function that works everywhere. First identify your database and version, then choose the matching example below.

What You’ll Need

  • Access to a SQL editor or query tool connected to your database.
  • The database product and version, such as SQL Server, PostgreSQL, MySQL, or Oracle. Split functions and their behavior vary between them.
  • A sample string and its delimiter, such as red,blue,green separated by commas. Decide whether empty values or the original order matter.
  • Permission to run a read-only query against the data you want to inspect. The examples here return results; they do not alter stored data.

Step-by-Step

  1. Confirm which database you are using. Check your connection settings or ask your database administrator. A query copied from SQL Server may fail in PostgreSQL, MySQL, or Oracle because each product has its own functions. Also check the version: a function may exist in one release but not an older one.
  2. Choose whether you need rows or columns. Splitting usually means turning one string into multiple rows. For example, a value such as red,blue,green becomes three rows. If you need a fixed number of columns instead, use the database’s position-specific functions or pivoting tools; a split-to-rows function is not automatically a column splitter.
  3. Try the SQL Server version for a SQL Server database. In SQL Server 2016 and later, with database compatibility level 130 or higher, use STRING_SPLIT. The following query turns a comma-separated sample into one row per value:
    SELECT value
    FROM STRING_SPLIT('red,blue,green', ',');

    The result contains red, blue, and green in separate rows. The output column is named value. The rows are not guaranteed to appear in the original order unless you request and sort by an ordinal, so do not rely on the display order from this basic query.

  4. Preserve order in newer SQL Server versions when needed. SQL Server 2022 and supported Azure SQL offerings allow the third argument, enable_ordinal. Use the constant 1 to return an ordinal column, then sort explicitly:
    SELECT ordinal, value
    FROM STRING_SPLIT('red,blue,green', ',', 1)
    ORDER BY ordinal;

    This returns positions 1, 2, and 3 alongside the values. The third argument must be a constant 0 or 1, not a variable or column expression. If your SQL Server version does not support ordinals, use a version-appropriate splitting method or handle ordering before the data is stored; do not assume the basic function’s row order is stable.

  5. Split a table column in SQL Server. Pass the column to the function using CROSS APPLY. Suppose a table named Products has a Tags column containing comma-separated tags:
    SELECT p.ProductID, s.value AS Tag
    FROM Products AS p
    CROSS APPLY STRING_SPLIT(p.Tags, ',') AS s;

    The result has one row per product-tag pair. A product with three tags appears on three rows. This is useful for searching or reporting on the individual values, but it does not update the original table. If Tags is NULL, SQL Server’s splitter produces no values for that input.

  6. Use PostgreSQL’s array functions in PostgreSQL. Convert the string into an array with string_to_array, then expand it into rows with unnest:
    SELECT unnest(string_to_array('red,blue,green', ',')) AS value;

    To keep each value’s position, use WITH ORDINALITY:

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

    Here the position starts at 1. PostgreSQL’s string_to_array also accepts a NULL delimiter to split into individual characters, but that is different from splitting on a comma. Use an explicit delimiter for ordinary delimited fields.

  7. Use a method designed for your MySQL version. MySQL does not provide a general built-in function named STRING_SPLIT. SUBSTRING_INDEX can retrieve a particular segment when you know its position, but it does not by itself return an arbitrary-length list as rows. For a reusable row-based split, consider storing the values in a normalized child table, or use a carefully written recursive common table expression in MySQL 8.0 or later. Test that approach against empty values, repeated delimiters, and long inputs before using it in production. Avoid assuming that a comma-separated string is valid JSON unless it has actually been formatted and escaped as JSON.
  8. Use Oracle-specific expressions in Oracle. Oracle does not use SQL Server’s STRING_SPLIT. One common approach uses REGEXP_SUBSTR with a hierarchical query to extract each token. The exact expression should account for your Oracle version and whether empty tokens need to be preserved. If the values are frequently queried as separate items, a child table is generally more reliable than repeatedly parsing a delimited text field.
  9. Check the output before using it in a larger query. Run the split against a short known string first. Count the returned rows, inspect the values, and verify their order if order matters. Then join or filter the split results as needed. When the source is a table, include its key in the result so you can tell which original record each token came from.
  10. Trim or convert values deliberately. A token may contain spaces, such as blue in red, blue. Some split functions preserve that leading space. Apply the trimming function supported by your database if spaces are not meaningful. If the values should be numbers or dates, convert them only after checking for invalid tokens; a conversion error can otherwise stop the whole query.

Common Mistakes

Using the wrong dialect is a frequent source of errors. A query containing STRING_SPLIT will not run unchanged in PostgreSQL, and PostgreSQL’s unnest syntax is not SQL Server syntax. Match the example to the database engine rather than changing function names at random.

Assuming the output keeps its input order can silently produce incorrect results. Unless the database function returns positions and the query sorts by them, row order is not guaranteed. Add an ordinal where supported and use ORDER BY on it.

Overlooking spaces and empty tokens changes what a value appears to be. A string like red,,green has an empty segment between two commas, while red, blue may produce a token with a leading space. Check how your database handles these cases and decide whether to keep, trim, or discard them.

Confusing splitting with permanent data cleanup is another pitfall. A SELECT query only shapes its result; it does not turn the original column into separate stored records. If applications repeatedly need to filter, validate, or join individual items, store them in a related table instead of parsing the same text on every query.

Using a delimiter that appears inside a value can split the data incorrectly. A basic comma split cannot tell whether a comma is a separator or part of a quoted value. For CSV-style data with quotes and escaped characters, use a proper CSV parser during import rather than treating the text as a simple delimited string.

Tips

  • Test with ordinary, empty, NULL, repeated-delimiter, and whitespace-containing inputs before applying a split to a whole table.
  • Use the simplest delimiter that matches the data. Many built-in split functions accept a single-character delimiter; do not assume a multi-character separator will work.
  • Give output columns clear aliases, such as Tag or Token, especially when joining split results to other tables.
  • Keep an original row ID in the output so each token can be traced back to its source record.
  • For data that is routinely searched or joined, consider one row per value in a normalized table. Splitting is convenient for occasional imports and queries, but it is not a substitute for a suitable data model.
  • Check execution cost on large tables. Splitting every row at query time can consume more resources than filtering a properly indexed related table.

Troubleshooting

“Invalid object name” or “function does not exist”: Verify the database engine, spelling, and version. In SQL Server, check that the server is SQL Server 2016 or later and that the database compatibility level is at least 130. In other engines, use that product’s native method rather than the SQL Server function.

The SQL Server third argument is rejected: Your SQL Server environment may not support ordinals, or the database compatibility level may be too low. Try the two-argument form first. If it works, check the server release and compatibility settings; do not pass a variable as the third argument where a constant is required.

The query returns fewer values than expected: Inspect the exact source string and delimiter. Confirm that the delimiter character is the same (for example, a comma rather than a semicolon), and check for NULL input or empty segments. If the text contains quoted delimiters, use a CSV-aware parser rather than a simple split function.

Values have unwanted spaces: Apply the database’s trimming function to each returned token. First inspect the raw results so you do not accidentally remove spaces that are meaningful in your data. Also check for tabs or other whitespace that ordinary space trimming may not remove.

The results appear in a different order: Add an ordinal output where the database supports it and sort with ORDER BY. If the method you use has no ordinal, it may not be possible to reliably reconstruct the original sequence from the returned rows alone.

A numeric or date conversion fails: One or more tokens may be blank or malformed. Select and validate the raw tokens first, remove or handle invalid values according to your rules, and then convert using your database’s safe conversion features where available.

Frequently Asked Questions

Is there one SQL function called STRING_SPLIT that works in every database?
No. SQL Server provides STRING_SPLIT, but PostgreSQL, MySQL, and Oracle use different functions or query patterns. Confirm your database before using an example.

Does STRING_SPLIT preserve the original order?
Do not rely on the basic SQL Server function to preserve order. On supported SQL Server 2022 and Azure SQL versions, request the ordinal column with the third argument set to 1, then sort by ordinal.

Can I split a string into columns instead of rows?
Yes, but the approach depends on the database and whether the number of values is fixed. You can select specific positions with database-specific expressions, or reshape the rows afterward. For a variable number of values, rows are usually easier to query.

What happens when the input is NULL or empty?
Behavior differs by database and function. SQL Server’s STRING_SPLIT returns an empty table for NULL input. Empty strings, empty segments, and consecutive delimiters may behave differently across engines, so test the cases your data can contain.

Can I split on more than one character?
It depends on the function. SQL Server’s delimiter argument is a single character. Other databases may offer patterns or other approaches, but a multi-character separator needs a method designed for it; verify the function’s documented behavior.

Should I store a list of values in one column?
Usually not if you often need to search, join, validate, or update individual values. A related table with one row per value is easier to index and maintain. A delimited string can still be practical for small, occasional imports or display-only data.

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.