We will discuss the definition of STRING_SPLIT(), its operation, and the new ordinal feature in SQL Server 2022 in this post.

String_Split()
In SQL Server 2016, STRING_SPLIT() was initially offered as a method for dividing a delimited string into rows. When working with data stored in a delimited format, such comma-separated values (CSV), this function is really useful. In the past, developers had to divide strings manually or using user-defined functions, which were frequently laborious and inefficient.

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

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

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.

HostForLIFE.eu SQL Server 2022 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.