How do I enable ordinals from the STRING_SPLIT function in MSSQL

Question

I'm trying to use the STRING_SPLIT function in Microsoft SQL Server 2019. The function works, if I only put in two arguments, but since I want to extract a specific element from the string, I would like to enable ordinals. When I add the third argument to the STRING_SPLIT function it returns

Msg 8144, Level 16, State 3, Line 5 Procedure or function STRING_SPLIT has too many arguments specified.

I don't understand what I'm doing wrong, since hovering over the STRING_SPLIT function clearly states that the function can take a third argument as an int.

My SQL code is as follows

SELECT *
FROM STRING_SPLIT('[Control Structure].Root.NP_02.ABC01_02_03.Applications.Prototype.Control Modules.ABC060V.ABC060VXFR2','.',1)
WHERE ORDINAL = 4

Answer 1

You can't enable it, since it is not available in SQL Server 2019 (and is almost certainly not going to be back-ported there).

The problem is that SSMS has IntelliSense / tooltips coded without conditional logic based on version, and the code is ahead of the engine. Currently the functionality is only available in Azure SQL Database, Managed Instance, and Synapse.

From the documentation:

The enable_ordinal argument and ordinal output column are currently only supported in Azure SQL Database, Azure SQL Managed Instance, and Azure Synapse Analytics (serverless SQL pool only).

Some more background:

• Trusting STRING_SPLIT() order in Azure SQL Database

What you can do instead is create your own inline table-valued UDF that provides the same type of ordinal output (and make it return the same output as STRING_SPLIT to make it easy to change later). There are many variations on this; here's one:

CREATE FUNCTION dbo.SplitStrings_Ordered
(
    @List       nvarchar(max),
    @Delimiter  nvarchar(255)
)
RETURNS TABLE
AS
    RETURN (SELECT value = Item ,
      ordinal = ROW_NUMBER() OVER (ORDER BY Number), 
    FROM (SELECT Number, Item = SUBSTRING(@List, Number, 
      CHARINDEX(@Delimiter, @List + @Delimiter, Number) - Number)
     FROM (SELECT ROW_NUMBER() OVER (ORDER BY s1.[object_id])
      FROM sys.all_objects AS s1 CROSS JOIN sys.all_objects AS s2) AS n(Number)
      WHERE Number <= CONVERT(INT, LEN(@List))
      AND SUBSTRING(@Delimiter + @List, Number, LEN(@Delimiter)) = @Delimiter
    ) AS y);
GO

Another simpler way would be to use JSON, which I forgot I even wrote recently in this tip:

CREATE FUNCTION dbo.SplitStrings_Ordered
(
  @List      nvarchar(max),
  @Delimiter nchar(1)
)
RETURNS table WITH SCHEMABINDING
AS
  RETURN
  (
    SELECT value, ordinal = [key]
    FROM OPENJSON(N'["' + REPLACE(@List, @Delimiter, N'","') + N'"]') AS x
  );
GO

Also, if you're just trying to get the last ordinal in a (1-)4-part name and each part is <= 128 characters, you can use PARSENAME():

DECLARE @str nvarchar(512) = N'here is one.here is two.and three.and four';

SELECT p1 = PARSENAME(@str, 4),
       p2 = PARSENAME(@str, 3),
       p3 = PARSENAME(@str, 2),
       p4 = PARSENAME(@str, 1);

Output:

p1	p2	p3	p4
here is one	here is two	and three	and four

• Example db<>fiddle