Split in SQL: Practical Methods for Common Databases

How-to basics · Updated November 22, 2026

To split a delimited string in SQL, use a function supported by your database: for example, PostgreSQL’s string_to_array, SQL Server’s STRING_SPLIT, or MySQL’s SUBSTRING_INDEX. First decide whether you need separate columns or separate rows, then check your database’s syntax; SQL has no single split function that works everywhere.

What You’ll Need

  • A database connection and permission to run a SELECT query.
  • The database product and version you are using, such as PostgreSQL, SQL Server, MySQL, or Oracle. Split functions differ between them.
  • A sample value and its delimiter: for example, red,green,blue uses a comma.
  • A clear idea of the desired result: one column per part, or one result row per part.

Step-by-Step

  1. Identify your database and the shape of the result you want. Find the product and version in your database client or ask your administrator. Then decide what “split” means for your task. If an address such as North,West needs to become two columns on the same row, you need positional extraction. If each color in red,green,blue should become its own row, you need a row-splitting method. These are different operations, and choosing the wrong one is a common reason a query returns an unexpected shape.
  2. Check the source data and delimiter. Inspect a few real values before writing the query. Confirm whether the separator is always a comma, pipe, space, or another character, and whether values can contain that character themselves. Look for leading or trailing delimiters, repeated delimiters, blank strings, and NULLs. For example, red,,blue has an empty middle part if repeated commas represent missing values. Do not assume every record follows the neat format of a sample.
  3. Use a positional function when you need a particular part. PostgreSQL offers split_part. With a literal value, SELECT split_part('red|green|blue', '|', 2) AS second_part; returns green. The final argument is the part number, starting at 1. You can use a table column in place of the literal, such as split_part(product_code, '-', 2). This is useful for retrieving one known part, but it does not create a variable number of columns or rows. If the requested part does not exist, PostgreSQL returns an empty string.
  4. For PostgreSQL, turn the parts into rows when needed. The following query splits a value on commas and includes each part’s position:
    SELECT part, position
    FROM unnest(string_to_array('red,green,blue', ','))
         WITH ORDINALITY AS pieces(part, position);

    It returns three rows: red at position 1, green at position 2, and blue at position 3. To split a table column, replace the literal in string_to_array with that column. Keeping the position is helpful when you need to reconstruct the original order.

  5. For SQL Server, use STRING_SPLIT for rows. This example works on supported SQL Server versions and returns one row for each part:
    SELECT value
    FROM STRING_SPLIT('red,green,blue', ',');

    Do not rely on the returned row order: SQL Server does not guarantee that STRING_SPLIT returns values in their original order. SQL Server 2022 and supported Azure SQL services also provide an ordinal option. Where available, use SELECT ordinal, value FROM STRING_SPLIT('red,green,blue', ',', 1) ORDER BY ordinal; to get positions. Check that your SQL Server version and database compatibility level support that option before using it. For older versions, a different splitting method may be required.

  6. For MySQL, use a recursive CTE for a simple row split. MySQL 8.0 supports recursive common table expressions. This example generates one position per comma-separated part and extracts the value at that position:
    WITH RECURSIVE numbers AS (
      SELECT 1 AS n
      UNION ALL
      SELECT n + 1
      FROM numbers
      WHERE n < 1 + LENGTH('red,green,blue')
                      - LENGTH(REPLACE('red,green,blue', ',', ''))
    )
    SELECT n,
           SUBSTRING_INDEX(
             SUBSTRING_INDEX('red,green,blue', ',', n),
             ',', -1
           ) AS value
    FROM numbers;

    The result has positions 1, 2, and 3 with values red, green, and blue. This is a compact example for a known string; adapting a recursive CTE to split a column for many source rows takes extra care. MySQL’s SUBSTRING_INDEX alone is often more convenient when you only need a specific part.

  7. For Oracle, use a regular expression when splitting into rows. For a simple comma-delimited value, this query extracts each nonempty part:
    SELECT LEVEL AS position,
           REGEXP_SUBSTR('red,green,blue', '[^,]+', 1, LEVEL) AS value
    FROM dual
    CONNECT BY REGEXP_SUBSTR('red,green,blue', '[^,]+', 1, LEVEL) IS NOT NULL;

    LEVEL numbers the results. The pattern finds runs of characters other than a comma, so this approach skips empty parts between repeated commas. If empty fields matter, use a method designed to preserve them rather than assuming this expression will return every position.

  8. Test edge cases and adapt the query to your table. Once the basic example works, try a value with one part, a missing part, a trailing delimiter, and NULL. Then substitute your column and table names and run a limited SELECT before using the result in an update or insert. If you plan to store split values, check whether your data model should instead keep each value in a separate related row. Splitting text at query time is convenient, but it can be a sign that a repeating list is stored in a single field.

Common Mistakes

Assuming every database uses the same function. A query using STRING_SPLIT will not run unchanged in PostgreSQL or MySQL. Confirm the database engine before copying syntax, and check whether a feature requires a particular version.

Expecting a guaranteed order. A result set has no guaranteed order without an ORDER BY. This matters especially with SQL Server’s STRING_SPLIT. Use an ordinal column when the database provides one, and order by it in the outer query.

Ignoring empty values and NULLs. A string that starts or ends with a delimiter, or contains two delimiters together, can produce empty parts—or have them skipped—depending on the method. NULL is also different from an empty string. Check how your chosen function handles both and decide what your application should do.

Splitting on a character that occurs inside a value. A comma-separated list is ambiguous if a value can itself contain an unquoted comma. For example, Paris, France,London cannot be reliably divided into two cities by simply splitting on every comma. Properly formatted CSV needs quote-aware parsing; a basic delimiter function is not a CSV parser.

Turning a split result into permanent changes too soon. First inspect the output with a read-only query. A mistaken delimiter or position can silently create incorrect records when used in an update or insert.

Tips

  • Use a short, realistic test value before applying the expression to a large table.
  • Keep the delimiter visible in the query and use the exact character found in the data. A hyphen, en dash, and em dash are different characters.
  • Trim whitespace around parts when appropriate, but do not strip spaces blindly if they are meaningful. For example, PostgreSQL’s btrim can remove surrounding whitespace from a result.
  • Return an ordinal or position alongside each part if order matters. It also makes it easier to diagnose missing or repeated values.
  • Use a safe, read-only test first. For a production update, preview affected rows and follow your team’s transaction and backup practices.
  • If you control the schema, store repeating values in separate rows instead of joining them into a delimited string. This makes filtering, validation, and indexing more straightforward.

Troubleshooting

The query reports an unknown function or syntax error. Verify the database engine, version, and compatibility settings. A function name that is valid in one product may not exist in another. Check the product’s documentation and use the syntax for that exact version.

The query returns one long value instead of several parts. Check that you are using a row-producing method rather than a function that extracts one position. Also verify that the delimiter in the expression exactly matches the delimiter in the stored text.

Parts appear in a different order. Add a position to the split result if your database supports it, then sort with ORDER BY. Without an explicit ordering column, the database is free to return rows in any order.

Some parts are missing or blank. Test repeated and trailing delimiters directly. The selected function may skip empty parts, return empty strings, or treat NULL differently. If blanks represent meaningful positions, choose a method that preserves them and verify the result before using it downstream.

Values containing delimiters split incorrectly. A simple split cannot infer which delimiters are part of a value and which divide values. Use a proper parser for quoted formats such as CSV, or change the data format so values are stored unambiguously.

Frequently Asked Questions

Is there a standard SQL split function?
No. SQL implementations provide different functions and techniques. Identify your database first, then choose its supported method.

How do I split one string into multiple columns?
Extract a known position for each output column using the function supported by your database. For example, PostgreSQL’s split_part(text, delimiter, position) can retrieve the first and second parts separately. This works best when the number and meaning of parts are predictable.

How do I split a string into rows?
Use a row-producing function or query: PostgreSQL can combine string_to_array with unnest, SQL Server provides STRING_SPLIT, and other databases have their own approaches. Add and sort by a position if the original order matters.

Can SQL split a comma-separated list while preserving commas inside quotes?
Not reliably with a basic split function. Quoted CSV has escaping and quote rules that a simple delimiter split does not interpret. Parse it with a CSV-aware tool or import process before querying the individual fields.

What happens when the string contains two delimiters in a row?
It may indicate an empty part, such as the blank value in red,,blue. Whether that part is returned depends on the database method. Test this case and decide whether to preserve, remove, or flag empty values.

Should I store lists as comma-separated text?
Usually not when you need to search, update, or validate individual items regularly. Store each item in its own row in a related table where possible. A delimited string can be acceptable for display or interchange, but it is harder to query reliably.

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.