SQL has no single, universal SPLIT command: the function depends on the database system you use. PostgreSQL has functions such as split_part, SQL Server has STRING_SPLIT, and BigQuery has SPLIT; other systems use different techniques or lack a built-in splitter.
To split a delimited string, first identify your database and decide whether you want one piece of text, several columns, or one result row per piece. Those goals call for different SQL, and details such as ordering and empty values can vary between database engines.
What splitting means in SQL
Splitting separates a string wherever a chosen delimiter appears. For example, splitting red|green|blue on the pipe character produces three values: red, green, and blue. A delimiter may be a comma, space, slash, or another character or string, depending on the database function.
There are three common ways to use the result:
- Return one part: retrieve the second item from a delimited value.
- Return an array or list: keep the pieces together as a collection.
- Return rows: turn each piece into a separate row, useful when joining or filtering individual values.
SQL syntax differs because the SQL standard does not define one general-purpose SPLIT function that works identically across databases. A query written for one engine may fail or behave differently in another.
Choose syntax for your database
Before copying an example, check which database runs the query. SQL Server, PostgreSQL, MySQL, Oracle, SQLite, and cloud warehouses have different string functions, supported options, and version requirements. Also check whether you need to preserve the original order: a result displayed in a particular order is not necessarily guaranteed to stay that way unless the query explicitly orders it.
Use this quick guide to find a starting point:
- PostgreSQL: use
split_partfor a particular position, orstring_to_arraywithunnestfor rows. - SQL Server: use
STRING_SPLITfor a row per value. - MySQL: use
SUBSTRING_INDEXfor position-based extraction; it does not directly return an arbitrary number of rows. - BigQuery: use
SPLIT, thenUNNESTto produce rows. - Oracle: regular-expression functions can extract tokens, with care around empty values and performance.
- SQLite: there is no built-in general-purpose string-splitting function.
PostgreSQL: split into a part or rows
In PostgreSQL, split_part returns the field at a specified position. Positions start at 1, so the following expression returns green, the second item:
SELECT split_part('red|green|blue', '|', 2);
Use string_to_array when an array is useful, or combine it with unnest to turn the array into rows:
SELECT part
FROM unnest(string_to_array('red|green|blue', '|')) AS parts(part);
This returns one row per item. You can use that pattern in a larger query, for example by applying it to a column with a lateral join. If row order matters, retain an ordinal position rather than assuming the output order of an expanded collection. PostgreSQL supports ordinality with WITH ORDINALITY, which can be used to make the position explicit.
SQL Server: use STRING_SPLIT
SQL Server provides STRING_SPLIT, which returns a table of values. This query produces rows containing the three values:
SELECT value
FROM STRING_SPLIT('red,green,blue', ',');
Do not assume the rows come back in the same order as the original string. In SQL Server 2022 and supported Azure SQL offerings, the optional third argument can request an ordinal column. Order by that column when sequence is important:
SELECT value, ordinal
FROM STRING_SPLIT('red,green,blue', ',', 1)
ORDER BY ordinal;
The ordinal option is version- and service-dependent, so verify that the target SQL Server supports it. The separator accepted by STRING_SPLIT is a single character, not an arbitrary multi-character string. The function also has compatibility requirements; SQL Server databases generally need compatibility level 130 or higher for STRING_SPLIT. Check the database version and compatibility setting if the function is not recognized.
MySQL: extract positions with SUBSTRING_INDEX
MySQL commonly uses SUBSTRING_INDEX to return text before or after a chosen occurrence of a delimiter. To get the second item from red|green|blue, first take the text through the second delimiter occurrence, then take the part after its last delimiter:
SELECT SUBSTRING_INDEX(
SUBSTRING_INDEX('red|green|blue', '|', 2),
'|', -1
);
The result is green. This can be handy when the position is known, such as extracting a fixed field from a value. It is not a direct equivalent of STRING_SPLIT: it does not automatically turn every token into a row. If you regularly need to search, join, or filter individual items, storing those items in separate rows is often a better design than repeatedly parsing a delimited column.
BigQuery: SPLIT and UNNEST
BigQuery’s SPLIT function returns an array. Use UNNEST when you want one row per element:
SELECT value
FROM UNNEST(SPLIT('red,green,blue', ',')) AS value;
To keep the position, use WITH OFFSET. BigQuery offsets start at 0, unlike the 1-based position used by PostgreSQL’s split_part:
SELECT value, offset
FROM UNNEST(SPLIT('red,green,blue', ',')) AS value
WITH OFFSET AS offset
ORDER BY offset;
The array result can also be useful without expanding it into rows. For example, array operations can let you inspect or transform elements while keeping the data in one record. Choose between an array and separate rows based on what the next step in your query needs.
Oracle and SQLite approaches
Oracle does not provide a direct general-purpose function with the same behavior as SQL Server’s STRING_SPLIT. One option is REGEXP_SUBSTR to find successive tokens. For example, a hierarchical query can extract comma-separated values:
SELECT REGEXP_SUBSTR('red,green,blue', '[^,]+', 1, LEVEL) AS value
FROM dual
CONNECT BY REGEXP_SUBSTR('red,green,blue', '[^,]+', 1, LEVEL) IS NOT NULL;
This pattern treats a token as one or more characters other than a comma, so it skips empty tokens between repeated delimiters. More complex input may need different handling. Regular expressions can also cost more than simpler string operations on large datasets, so consider the expected volume and test the query on representative data.
SQLite has no built-in general-purpose split function. For a small, fixed number of fields, expressions using functions such as instr and substr may be enough, but those expressions become difficult to maintain when the number of tokens varies. Applications can split values before inserting them, or use a custom function if the environment allows one. For recurring database queries, a normalized table is usually easier to work with than a homemade splitter.
Edge cases that change the result
A basic split works best when the input follows a simple, consistent format. Real data often includes exceptions, and different functions do not necessarily treat them alike. Check how your database handles these cases before relying on the output:
- Missing delimiter: a function that asks for a particular field may return the original string, an empty value, or another result depending on the engine and function.
- Repeated delimiters: a value such as
red||bluemay represent an empty middle field, or a splitter may skip it. - Leading or trailing delimiters:
|red|has empty fields at its ends in some interpretations. - NULL input: functions commonly return NULL or no rows, but verify the behavior before using the result in joins or filters.
- Multi-character delimiters: some functions accept them and others, including SQL Server’s
STRING_SPLIT, require a single-character separator.
Another frequent mistake is treating delimited text as CSV. Splitting on commas alone does not correctly parse every CSV value: a field may contain a quoted comma, and quotes may be escaped. For example, a CSV record containing red,"green,blue",yellow has three fields, not four. Use a proper CSV parser when the input follows CSV quoting rules rather than trying to handle it with a plain delimiter split.
Splitting versus storing values separately
Splitting text inside a query is useful when importing legacy data, cleaning a one-time input, or processing a field that is genuinely supplied as a delimited string. It is less suitable as a permanent substitute for relational structure. If a customer record contains a comma-separated list of categories, searching for one category can be error-prone, and updates can require rebuilding the string.
When each item needs its own identity, filtering, relationship, or update, store the items as rows in a related table. A one-to-many table makes it easier to enforce constraints, join on individual values, and index them. When the input is an external text format, parse and validate it at ingestion, then store the resulting values in the structure your queries need. The best choice depends on whether splitting is occasional cleanup or a routine part of application logic.
Frequently Asked Questions
Is there a universal SPLIT function in SQL?
No. SQL databases provide different functions and syntax, and some do not offer a built-in general-purpose splitter. Use the function documented for your database, and check its version-specific behavior before using it in portable code.
How do I split a string into rows in SQL?
Use a table-valued or array-expansion approach supported by your database. Examples include SQL Server’s STRING_SPLIT, PostgreSQL’s unnest(string_to_array(...)), and BigQuery’s UNNEST(SPLIT(...)). The exact syntax and ordering behavior differ.
How do I get the second value after splitting?
In PostgreSQL, use split_part(text, delimiter, 2). In MySQL, nested SUBSTRING_INDEX calls can extract a known position. Other databases have their own methods, so there is no one expression that works everywhere.
Does STRING_SPLIT preserve the original order?
Do not rely on its output order unless you explicitly sort the results. SQL Server versions that support the ordinal option can return a position column; sort by that column to restore the input sequence.
Can I split a CSV string with a SQL split function?
Not safely when fields can contain quoted delimiters or escaped quotes. A simple split separates every matching delimiter, even one that is part of a quoted field. Use a CSV-aware parser for CSV input, then load the parsed fields into SQL.
Related
- Snowflake SPLIT_PART: Syntax, Examples & Tips
- Snowflake SPLIT_PART: Syntax, Examples, and Common Issues
- JavaScript split() Explained: Strings, Limits, and Examples
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Text to Columns Greyed Out? Causes and FixesIf Text to Columns is greyed out in Excel for Microsoft 365 on Windows, first select a cell…
- Large Excel File Won’t Open? Causes and Practical FixesIf a large Excel file won’t open, first copy it to a local drive, close other programs, and…
- Does Excel Have a Debt Payoff Template? What to KnowYes. Excel can provide debt payoff templates through its template gallery or online template sources, though one may…
- How to Split Cells in Half in Excel: 3 Practical MethodsTo split the contents of a cell, select it and use Data > Text to Columns to separate…
- Excel Hide Columns Based on Date: Methods That WorkYes—Excel can hide columns according to dates, but a formula alone cannot change a column’s hidden state. For…
- Excel Printer Not Responding? Troubleshoot the ProblemIf Excel says the printer is not responding, first check that Windows has the right printer selected and…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.