In SQL Server 2022, an enhancement came in STRING_SPLIT() function, which was a long awaited functionality. This enhancement provides a new column name ordinal. I have written an article about it in detail.
Recently, I was using STRING_SPLIT() function and came across an error as mentioned below.
Error:
Invalid column name ‘ordinal’.
Example:
In the given below example, I wrote a simple script by using STRING_SPLIT() function and placed an ordinal column in the select statement. This ordinal column provides the row number for each split string which is a very handy functionality but I got an error as shown below.
USE Northwind
GO
SELECT CategoryID
, CategoryName
, Value AS Description
, Ordinal
FROM Categories
CROSS APPLY STRING_SPLIT(CONVERT(VARCHAR(100),Description), ',');
GO
--OUTPUT
Solution:
Remember, whenever you place ordinal column in select statement, you MUST pass “1” as third argument in STRING_SPLIT() function. By default, it takes third argument as “0” which was the case in the above example and in such cases, this function does NOT enable ordinal column in the select statement and resulted in error.
Let’s place ordinal column in the select statement and the MOST important thing, we MUST pass “1” in the third argument of STRING_SPLIT() function. This time, it will run sucessfully as shown below.
USE Northwind
GO
SELECT CategoryID
, CategoryName
, Value AS Description
, Ordinal
FROM Categories
CROSS APPLY STRING_SPLIT(CONVERT(VARCHAR(100),Description), ',', 1);
GO
--OUTPUT
Conclusion:
Whenever you place ordinal column in select statement, you MUST pass “1” as third argument in STRING_SPLIT() in order to avoid this error.
SQL Server Portal 