STRING_SPLIT() Ordinal New T-SQL Enhancements in SQL Server

In this article, we will explore what STRING_SPLIT() is, how it works, and the new ordinal enhancement in SQL Server 2022.

STRING_SPLIT()

STRING_SPLIT() was first introduced in SQL Server 2016 as a way to split a delimited string into rows. This function is extremely helpful when working with data stored in a delimited format, such as comma-separated values (CSV). Previously, developers had to rely on user-defined functions or other manual methods to split strings, which were often inefficient and cumbersome.

syntax

STRING_SPLIT (string , separator);
--string: The string to be split.
--separator: The character used to separate the string into parts.

Example

SELECT value
FROM STRING_SPLIT('STRING_SPLIT,New,SQL,Server,2022,Enhancement', ',')

Output

SQL Server

While this functionality has been useful, one limitation was the lack of an ordinal position for the split elements. In SQL Server 2016–2019, the function returned only the values from the split operation but did not indicate the position (or index) of each value within the original string.

New Ordinal Enhancement in SQL Server 2022

In SQL Server 2022, the STRING_SPLIT() function now includes an optional ordinal parameter, which allows you to retrieve not only the split values but also the original position (or index) of each value within the string. This enhancement is particularly useful when the order of the elements matters, such as when splitting CSV data that maps to specific columns or when processing structured text data.

Syntax

STRING_SPLIT (string , separator [,enable_ordinal]);
--enable_ordinal: A bit flag (0 or 1) that specifies whether to include the ordinal column in the output. 
--When set to 1, the result set includes both the value and the ordinal position of each element in the string.

Example

SELECT value, ordinal
FROM STRING_SPLIT('STRING_SPLIT,New,SQL,Server,2022,Enhancement', ',', 1)

Output

Output

In this example, the addition of the ordinal column shows the position of each split element in the original string. This enhancement simplifies working with ordered data, where the sequence is important for further processing.

Why the Ordinal Enhancement Matters?

For SQL Developers, data engineers, and DBAs, the ordinal enhancement in STRING_SPLIT() offers several key benefits.

Improved Data Integrity and Validation: When working with structured data in delimited formats, such as CSV files, maintaining the correct sequence is crucial. By using the ordinal feature, you can ensure that the split data retains its original order, which is particularly useful when validating the contents of a file against expected formats.
Enhanced Data Parsing and Mapping: Many applications require data parsing and mapping to different columns or formats. For example, a CSV line might represent a row in a database table, where each column has a specific order. The ordinal feature makes it easier to split the string and map the resulting values to the correct columns.
Simplification of Existing Queries: Before SQL Server 2022, developers had to write additional code to maintain the order of split data. This often involved creating complex logic or using additional functions like ROW_NUMBER() or CROSS APPLY to simulate the ordinal behavior. Now, with the built-in ordinal feature, such queries are more straightforward, reducing code complexity and improving performance.
Improved Performance: The native implementation of STRING_SPLIT() with an ordinal column is optimized for performance. By reducing the need for additional functions or joins to maintain the order of split elements, the overall query execution is faster, especially when working with large datasets.

Conclusion

The introduction of the ordinal enhancement in STRING_SPLIT() in SQL Server 2022 is a significant improvement that simplifies and optimizes many common string manipulation tasks. By providing the ability to retain the original order of split elements, SQL developers, data engineers, and DBAs can now write cleaner, more efficient code when dealing with delimited data. The new ordinal feature can be particularly useful in scenarios such as data parsing, CSV file processing, and structured data validation. With this enhancement, SQL Server 2022 continues to build on its reputation as a powerful and flexible platform for handling a wide variety of data management tasks.