No: SQL does not have one universal built-in string-splitting function that works across every database. Some database systems provide their own split functions, while others require a workaround, and the syntax and results can differ.
What “built in” means in SQL
SQL is a family of related standards and implementations, not a single program with one fixed set of functions. A database engine such as Microsoft SQL Server, PostgreSQL, MySQL, Oracle Database, or SQLite supports its own features alongside common SQL syntax. As a result, a function name that works in one system may produce an error in another.
STRING_SPLIT is a built-in table-valued function in Microsoft SQL Server. It is not a universal SQL keyword or a function guaranteed by the SQL standard. Other databases may provide a different function that splits text, or a way to extract pieces of text without returning a table of tokens.
This distinction matters when copying a query from a tutorial, migrating an application, or switching database products. Before choosing a solution, identify the database engine and, where relevant, its version and compatibility settings.
How STRING_SPLIT works in SQL Server
In SQL Server, STRING_SPLIT takes an input string and a one-character separator, then returns a table with a row for each separated value. For example:
SELECT value FROM STRING_SPLIT('red,green,blue', ',');
The result contains the values red, green, and blue in separate rows. The function became available in SQL Server 2016, and the database compatibility level must be at least 130. If SQL Server reports that the function is invalid, check both the server version and the database compatibility level.
Do not assume the returned rows are in the same order as the original text. Without an explicit ordering rule, SQL Server does not guarantee row order. SQL Server 2022 and some Azure SQL offerings support an optional third argument that enables an ordinal column:
SELECT value, ordinal FROM STRING_SPLIT('red,green,blue', ',', 1) ORDER BY ordinal;
The ordinal identifies each token’s position. The third argument must be a constant 0 or 1; when enabled, the result includes the ordinal column. Check the documentation for the specific SQL Server or Azure product and version in use, since this option is not available in every older environment.
Equivalent approaches in other databases
There is no single substitute with exactly the same behavior everywhere. These examples show the general options, but details such as empty tokens, nulls, and ordering should be checked against the target engine’s documentation.
- PostgreSQL:
string_to_arrayturns delimited text into an array. Useunnestto return its elements as rows:SELECT unnest(string_to_array('red,green,blue', ',')) AS value;For a single known position,split_part('red,green,blue', ',', 2)returns green. PostgreSQL also providesregexp_split_to_tablefor splitting on a regular expression. - MySQL: MySQL does not provide a direct built-in equivalent to SQL Server’s row-returning
STRING_SPLIT.SUBSTRING_INDEXcan extract a part when the position is known, but it is not a general split-to-rows function. A recursive common table expression or a JSON-based approach may suit a particular task, with care taken to handle escaping and empty values. - Oracle Database: A common approach uses
REGEXP_SUBSTRwith a hierarchical query to extract successive matches. For simple comma-separated values, a pattern such as[^,]+can find each non-empty token. Because that pattern matches non-empty text, it does not preserve empty fields between consecutive commas. - SQLite: SQLite has no general-purpose built-in string-to-rows split function. A recursive common table expression can break a string into tokens, and JSON table functions can help when the input is valid JSON and the SQLite build supports those functions.
These are not drop-in replacements. For instance, PostgreSQL’s array result and SQL Server’s table result have different types and may need different handling in the surrounding query. A query designed for one product may need rewriting, not just a function-name change.
How to choose the right method
Start by asking what shape of result the application needs. If the goal is to retrieve only the second field, a position-specific function such as PostgreSQL’s split_part may be simpler than splitting every value into rows. If each token must be joined to another table, filtered, counted, or processed separately, a row-returning method is usually more useful.
- Confirm the database engine, version, and compatibility level.
- Decide whether the result should be one selected field, an array, or a row per token.
- Determine how the method should treat empty fields, null input, whitespace, and repeated separators.
- Check whether token order matters, and preserve or sort by a position value if it does.
- Test with representative data, including malformed or unusually long input.
If the query must run on several database products, consider keeping the split operation in application code or writing a database-specific implementation behind a shared interface. That can be more maintainable than assuming every engine supports the same SQL function.
Empty values, whitespace, and other common problems
A delimiter is not always a clean boundary between meaningful values. Consider red,,blue,: it has consecutive commas and a trailing comma. Depending on the function and method, the result may include empty strings, omit them, or handle the trailing field differently. SQL Server’s STRING_SPLIT can return empty substrings when separators occur consecutively. A regular-expression pattern that matches only non-empty text, such as the Oracle example above, skips them.
Whitespace is another frequent source of confusion. Splitting red, green at the comma may produce a token with a leading space before green. Splitting and trimming are separate operations; do not assume a split function removes spaces. You can trim values in a later query step using the trim function supported by the database, or clean the input before it reaches the database.
Also check what happens with a null input and with an empty string. A null is not the same as an empty string, and engines can return different results for each. If empty fields have meaning—for example, a blank middle value in a fixed-position record—choose an approach that preserves them rather than silently discarding them.
Splitting text versus fixing the data model
Splitting is useful when dealing with imported files, legacy records, user-supplied lists, or a delimited value that must be processed temporarily. It can be a practical step in a data-cleaning or migration query.
However, routinely storing multiple independent values in one column often makes later work harder. A comma inside a value, inconsistent spacing, or a changed delimiter can make parsing ambiguous. Searching for one exact item can also become less straightforward than querying a row-based structure.
When values need to be searched, updated, counted, or related independently, storing one value per row in a related table is usually a clearer relational design. Splitting a packed column at query time may remain necessary for older systems, but it is worth treating that as a constraint to manage rather than assuming the delimiter is a reliable long-term data format.
Performance and safe query design
Splitting a short string for a small result set is often straightforward, but repeatedly parsing large text values can add work to a query. The impact depends on the engine, data volume, query plan, and how often the operation runs. If performance matters, test using realistic data and inspect the query plan rather than assuming one technique is always fastest.
Splitting does not make dynamically constructed SQL safe. If a user supplies a delimited list and the application inserts its contents directly into a SQL statement, the query can still be vulnerable to injection or syntax errors. Use parameterized queries for values, and validate the format and permitted size of input. A delimiter parser is not a substitute for parameterization.
For a known small set of values, the application may be able to pass separate parameters or use a table-valued parameter where the database supports it. For larger or recurring sets, a temporary or staging table can provide a clearer structure. The best choice depends on the database and how the application sends data.
Frequently Asked Questions
Is STRING_SPLIT part of standard SQL?
No. The name STRING_SPLIT refers to a SQL Server function, not a function guaranteed across SQL implementations. Other database systems have different functions or require another method.
Why does SQL Server say STRING_SPLIT is not a recognized function?
Common causes include using a SQL Server version older than 2016 or a database compatibility level below 130. Verify the engine and database settings, and confirm that the query is running against SQL Server rather than another database product.
Does STRING_SPLIT preserve the original order?
Do not rely on the output order unless the query specifies one. In supported SQL Server versions, enable the ordinal output and order by that column when original position matters.
Can I split a string into columns instead of rows?
Sometimes, but a split function generally produces an array or rows, not a fixed set of columns. If the number and meaning of fields are known, use the database’s field-extraction functions or explicitly map the positions. For a changing number of values, rows are usually a more flexible result shape.
Which SQL database has the easiest split function?
That depends on the task. SQL Server has STRING_SPLIT for rows, while PostgreSQL offers both array and row-oriented options. MySQL and SQLite commonly need a different technique for row output. Compare the output type, ordering support, and treatment of empty tokens—not just how short the example query looks.
Related
- SQL Server STRING_AGG: Syntax, Examples, and Limits
- How to Split an Activity in P6: Step-by-Step Guide
- JavaScript String Operations: Methods 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
- Change to Light View in Outlook: Steps for Every VersionTo change Outlook to a light view, open its appearance or theme settings and choose Light, or turn…
- Google Sheets: How to Generate Unique IDsYes. Google Sheets can generate unique IDs with a row-based formula for simple lists, or with Google Apps…
- Remove a Dropdown List in Excel Without Losing DataTo remove a dropdown list in Excel for Microsoft 365 on Windows, select the cell or cells, go…
- How to Insert a Cursive Signature in Microsoft WordTo insert a cursive signature in Microsoft Word, add a clear image of your handwritten signature with Insert…
- Excel Shortcut to Switch Between Worksheet TabsIn Excel for Microsoft 365 on Windows, press Ctrl+Page Down to move to the next sheet tab, or…
- How to Add Page Numbers in Word Starting on Page 2To add page numbers in Word from the second page, insert a page number, then turn on Different…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.