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
SELECTquery. - 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,blueuses a comma. - A clear idea of the desired result: one column per part, or one result row per part.
Step-by-Step
- 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,Westneeds to become two columns on the same row, you need positional extraction. If each color inred,green,blueshould 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. - 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,,bluehas an empty middle part if repeated commas represent missing values. Do not assume every record follows the neat format of a sample. - 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;returnsgreen. The final argument is the part number, starting at 1. You can use a table column in place of the literal, such assplit_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. - 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:
redat position 1,greenat position 2, andblueat position 3. To split a table column, replace the literal instring_to_arraywith that column. Keeping the position is helpful when you need to reconstruct the original order. - For SQL Server, use
STRING_SPLITfor 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_SPLITreturns values in their original order. SQL Server 2022 and supported Azure SQL services also provide an ordinal option. Where available, useSELECT 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. - 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, andblue. 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’sSUBSTRING_INDEXalone is often more convenient when you only need a specific part. - 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;LEVELnumbers 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. - 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
SELECTbefore 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
btrimcan 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
- Oracle Split String Example: SQL Methods That Work
- Balance of Payments: How It Works
- Split by Position in Power Query: A Practical Guide
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Time Shortcut: How to Insert the Current TimeIn Excel for Microsoft 365 on Windows, select a cell and press Ctrl+Shift+; (semicolon) to enter the current…
- How to Double Freeze Panes in Excel: Rows and ColumnsTo double freeze panes in Excel—that is, keep both rows and columns visible while you scroll—select the cell…
- Excel Print Area Lines: How to View, Set, and Hide ThemYes. In Excel for Microsoft 365 on Windows, the clearest way to see your print area is to…
- Kids Chore List Excel Template: Build a Simple TrackerA kids chore list Excel template is a simple spreadsheet for listing household jobs, assigning them to children,…
- LibreOffice: How to Merge Table Cells in Writer and CalcYes. In LibreOffice Writer, select adjacent cells in a table and choose Table > Merge Cells; in Calc,…
- Create Labels in Word With Mail Merge: Step-by-StepTo create a sheet of address labels in Word, open the Mailings tab, choose Start Mail Merge >…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.