Transact SQL:- String_Split

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

Column # Column Name Datatype
1 Value It returns nvarchar if any of the input arguments are either nvarchar or nchar. Otherwise, it returns varchar

 

v2022

Column # Column Name Datatype
1 Value It returns nvarchar if any of the input arguments are either nvarchar or nchar. Otherwise, it returns varchar
2 Ordinal If the enable_ordinal argument is passed a value of 1, a second column named ordinal is returned that consists of the 1-based index values of each substring’s position in the input string.

 

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

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s