Background

As we stand on the door steps of SQL Server 2022, want to draw a little light on the STRING_SPLIT function.

STRING_SPLIT

Lineage

The String_Split function was introduced in SQL Server 2016.

Requirements

Database Settings

Database Compatibility Mode

The contextual database’s compatibility mode by set to at least 130.

BTW, with SQL Server 2022, our native database compatibility mode is now at 160.

Function

Function Arguments

v2016

When introduced in SQL Server v2016, here is the argument list for the String_Split function.

v2022

With SQL Server v2022, a new optional argument has been added.

The name of the argument is enable_ordinal.

 

Function Result

Columns

v2016

 

v2022

 

Sample Code

Sample -01

Code

declare @rhyme          varchar(8000)
declare @CHAR_SEPARATOR char(1)

set @rhyme 
    = 'Jack and Gill went up the hill '
      + 'To fetch a pail of water '
      + 'Jack fell down and broke his crown '
      + 'And Gill came tumbling after.'

set @CHAR_SEPARATOR = ' '

SELECT  
          itvfSS.ordinal

        , itvfSS.[value]

FROM   STRING_SPLIT

        (
              @rhyme
            , @CHAR_SEPARATOR
            , 1
        ) itvfSS

order by

    itvfSS.ordinal asc

;

Output

Lacks & Deficiencies

Microsoft is very clear about what an astute developer will see as lacks in String_Split’s current implementation.

Those lacks includes:-

1. Function Declaration
   • enable_ordinal
     • Note that the enable_ordinal argument must be a constant value, not a column or variable.
     • It must also be either a bit or int data type with a value of 0 or 1.
     • Otherwise, the function will raise an error.
2. Function Output Rows
   • The output rows might be in any order.
   • The order is not guaranteed to match the order of the substrings in the input string.

Code Sharing

1. Git
   • Gist
     • DanielAdeniji/TransactSQLStringSplitJackAndJill.sql
       Link

 

Summary

Offering up the position of each tokenized string is a welcome addition to the String_Split function.

In light of real-world use cases, there remains room for improvements.

 

References

1. Microsoft
   • Docs
     • String_Split ( Transact SQL )
       Link