How do I enable ordinals from the STRING_SPLIT function in MSSQL

user_15121460 0
Viewed 4687

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
ssms sql-server
2 Answers

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:

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
user_613050

SQL Server's XML/XQuery allow to implement very easy tokenization of a string.

XML/XQuery data model is based on ordered sequences.

It allows to retrieve any token based on its position is a string of tokens.

SQL

DECLARE @tokens VARCHAR(256) = '[Control Structure].Root.NP_02.ABC01_02_03.Applications.Prototype.Control Modules.ABC060V.ABC060VXFR2'
    , @separator CHAR(1) = '.'
    , @pos INT = 4;

SELECT c.value('(/root/r[sql:variable("@pos")]/text())[1]', 'VARCHAR(100)') AS token
FROM (SELECT TRY_CAST('<root><r><![CDATA[' + 
        REPLACE(@tokens, @separator, ']]></r><r><![CDATA[') + 
        ']]></r></root>' AS XML)) AS t(c);

Output

+-------------+
|    token    |
+-------------+
| ABC01_02_03 |
+-------------+
user_35156130
Related