Table of Contents
- Introduction
- Why the Ordinal Enhancement Matters
- New Ordinal Enhancement in SQL Server 2022
- Syntax of STRING_SPLIT() with Ordinal
- Example of STRING_SPLIT() with Ordinal
- Output Explanation
- Conclusion
Introduction
SQL Server 2022 has introduced a key enhancement to the STRING_SPLIT() function with the addition of the ordinal parameter. This enhancement allows the function to return an additional column indicating the position of each split substring in the original input.
Prior to this, the output order was not guaranteed, making it challenging to handle scenarios where the order of elements was important.
Why the Ordinal Enhancement Matters
In SQL Server 2016 and 2017, STRING_SPLIT() was a popular function for breaking strings into rows of substrings, but it didn’t preserve their original order. This limitation required developers to implement complex workarounds to retain the sequence of split values.
The new ordinal parameter in SQL Server 2022 resolves this issue, offering a native way to track the order of each split substring. This is particularly important for handling comma-separated values (CSV), ordered lists, and logs.
New Ordinal Enhancement in SQL Server 2022
The enhancement in SQL Server 2022 introduces the ability to include an ordinal column when using STRING_SPLIT(). The ordinal column shows the position of each substring in the original string, which was not possible in earlier versions. This enhancement provides developers with a reliable method for maintaining the order of elements without the need for additional sorting or custom functions.
Syntax of STRING_SPLIT() with Ordinal
The updated syntax for the STRING_SPLIT() function is as follows:
STRING_SPLIT ( string_expression, separator, [ enable_ordinal ] )
- string_expression: The string that you want to split.
- separator: The delimiter used to split the string (e.g., comma, space).
- enable_ordinal (optional): When set to
1, includes the ordinal column indicating the order of the split values.
Example of STRING_SPLIT() with Ordinal
Let’s look at an example of how the ordinal parameter works in SQL Server 2022. Suppose we have the following string:
SELECT value, ordinal
FROM STRING_SPLIT('apple,banana,orange,grape', ',', 1);
This query splits the string using a comma as the delimiter and includes the ordinal column.
Output Explanation
The result of the query is as follows:
| Value | Ordinal |
|---|---|
| apple | 1 |
| banana | 2 |
| orange | 3 |
| grape | 4 |
As seen in the output, the ordinal column retains the position of each split value in the original string. This makes it easy to maintain the order when manipulating or processing the split values.
Conclusion
The inclusion of the ordinal parameter in SQL Server 2022’s STRING_SPLIT() function addresses a long-standing challenge of maintaining the order of split values. This enhancement is particularly valuable when dealing with structured data, such as CSVs or logs, where order is critical. By using this new feature, developers can now simplify their queries and ensure data integrity without needing complex workarounds.
