return value at a position from STRING_SPLIT in SQL Server 2016

return value at a position from STRING_SPLIT in SQL Server 2016

There is – starting with v2016 – a solution via FROM OPENJSON():

DECLARE @str VARCHAR(100) = val1,val2,val3;

SELECT *
FROM OPENJSON([ +  REPLACE(@str,,,,) + ]);

The result

key value   type
0   val1    1
1   val2    1
2   val3    1

The documentation tells clearly:

When OPENJSON parses a JSON array, the function returns the indexes of the elements in the JSON text as keys.

For your case this was:

SELECT z_y_x AS splitIt
INTO #split UNION
SELECT a_b_c

DECLARE @delimiter CHAR(1)=_;

SELECT * 
FROM #split
CROSS APPLY OPENJSON([ +  REPLACE(splitIt,@delimiter,,) + ]) s
WHERE s.[key]=1; --zero based

Lets hope, that future versions of STRING_SPLIT() will include this information

UPDATE Performance tests, compare with popular Jeff-Moden-splitter

Try this out:

USE master;
GO

CREATE DATABASE dbTest;
GO

USE dbTest;
GO
--Jeff Modens splitter
CREATE FUNCTION [dbo].[DelimitedSplit8K](@pString VARCHAR(8000), @pDelimiter CHAR(1))
RETURNS TABLE WITH SCHEMABINDING AS
 RETURN
  WITH E1(N) AS (
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
                ),                          --10E+1 or 10 rows
       E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
       E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
 cteTally(N) AS (
                 SELECT TOP (ISNULL(DATALENGTH(@pString),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
                ),
cteStart(N1) AS (
                 SELECT 1 UNION ALL
                 SELECT t.N+1 FROM cteTally t WHERE SUBSTRING(@pString,t.N,1) = @pDelimiter
                ),
cteLen(N1,L1) AS(
                 SELECT s.N1,
                        ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0)-s.N1,8000)
                   FROM cteStart s
                )
 SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY l.N1),
        Item       = SUBSTRING(@pString, l.N1, l.L1)
   FROM cteLen l
;
GO
--Avoid first call bias
SELECT * FROM dbo.DelimitedSplit8K(a,b,c,,);
GO  

--Table to keep the results
CREATE TABLE Results(ID INT IDENTITY,ResultSource VARCHAR(100),durationMS INT, RowsCount INT);
GO
--Table with strings to split
CREATE TABLE dbo.DelimitedItems(ID INT IDENTITY,DelimitedNString nvarchar(4000),DelimitedString varchar(8000));
GO

–Get rows wiht randomly mixed strings of 100 items
–Try to play with the count of rows (count behind GO) and the count with TOP

INSERT INTO DelimitedItems(DelimitedNString)
SELECT STUFF((
            SELECT TOP 100 ,+REPLACE(v.[name],,,;) 
            FROM master..spt_values v
            WHERE LEN(v.[name])>0
            ORDER BY NewID()
            FOR XML PATH()),1,1,)
--Keep it twice in varchar and nvarchar
UPDATE DelimitedItems SET DelimitedString=DelimitedNString;
GO 500 --create 500 differently mixed rows

–The tests

DECLARE @d DATETIME2;

SET @d = SYSUTCDATETIME();
    SELECT DI.ID, DS.Item, DS.ItemNumber
    INTO #TEMP
    FROM dbo.DelimitedItems DI
         CROSS APPLY dbo.DelimitedSplit8K(DI.DelimitedNString,,) DS;
INSERT INTO Results(ResultSource,RowsCount,durationMS)
SELECT delimited8K with NVARCHAR(4000)
      ,(SELECT COUNT(*) FROM #TEMP) AS RowCountInTemp
      ,DATEDIFF(MILLISECOND,@d,SYSUTCDATETIME()) AS Duration_NV_ms_delimitedSplit8K

SET @d = SYSUTCDATETIME();
    SELECT DI.ID, DS.Item, DS.ItemNumber
    INTO #TEMP2
    FROM dbo.DelimitedItems DI
         CROSS APPLY dbo.DelimitedSplit8K(DI.DelimitedString,,) DS;
INSERT INTO Results(ResultSource,RowsCount,durationMS)
SELECT delimited8K with VARCHAR(8000)
      ,(SELECT COUNT(*) FROM #TEMP2) AS RowCountInTemp
      ,DATEDIFF(MILLISECOND,@d,SYSUTCDATETIME()) AS Duration_V_ms_delimitedSplit8K

SET @d = SYSUTCDATETIME();
    SELECT DI.ID, OJ.[Value] AS Item, OJ.[Key] AS ItemNumber
    INTO #TEMP3
    FROM dbo.DelimitedItems DI
         CROSS APPLY OPENJSON([ +  REPLACE(DI.DelimitedNString,,,,) + ]) OJ;
INSERT INTO Results(ResultSource,RowsCount,durationMS)
SELECT OPENJSON with NVARCHAR(4000)
      ,(SELECT COUNT(*) FROM #TEMP3) AS RowCountInTemp
      ,DATEDIFF(MILLISECOND,@d,SYSUTCDATETIME()) AS Duration_NV_ms_OPENJSON

SET @d = SYSUTCDATETIME();
    SELECT DI.ID, OJ.[Value] AS Item, OJ.[Key] AS ItemNumber
    INTO #TEMP4
    FROM dbo.DelimitedItems DI
         CROSS APPLY OPENJSON([ +  REPLACE(DI.DelimitedString,,,,) + ]) OJ;
INSERT INTO Results(ResultSource,RowsCount,durationMS)
SELECT OPENJSON with VARCHAR(8000)
      ,(SELECT COUNT(*) FROM #TEMP4) AS RowCountInTemp
      ,DATEDIFF(MILLISECOND,@d,SYSUTCDATETIME()) AS Duration_V_ms_OPENJSON
GO
SELECT * FROM Results;
GO

–Clean up

DROP TABLE #TEMP;
DROP TABLE #TEMP2;
DROP TABLE #TEMP3;
DROP TABLE #TEMP4;

USE master;
GO
DROP DATABASE dbTest;

Results:

200 items in 500 rows

1220    delimited8K with NVARCHAR(4000)
 274    delimited8K with VARCHAR(8000)
 417    OPENJSON with NVARCHAR(4000)
 443    OPENJSON with VARCHAR(8000)

100 items in 500 rows

421 delimited8K with NVARCHAR(4000)
140 delimited8K with VARCHAR(8000)
213 OPENJSON with NVARCHAR(4000)
212 OPENJSON with VARCHAR(8000)

100 items in 5 rows

10  delimited8K with NVARCHAR(4000)
5   delimited8K with VARCHAR(8000)
3   OPENJSON with NVARCHAR(4000)
4   OPENJSON with VARCHAR(8000)

5 items in 500 rows

32  delimited8K with NVARCHAR(4000)
30  delimited8K with VARCHAR(8000)
28  OPENJSON with NVARCHAR(4000)
24  OPENJSON with VARCHAR(8000)

–unlimited length (only possible with OPENJSON)
–Wihtout a TOP clause while filling
–results in about 500 items in 500 rows

1329    OPENJSON with NVARCHAR(4000)
1117    OPENJSON with VARCHAR(8000)

Facit:

  • the popular splitter function does not like NVARCHAR
  • the function is limited to strings within 8k byte volumen
  • Only the case with many items and many rows in VARCHAR lets the splitter function be ahead.
  • In all other cases OPENJSON seems to be more or less faster…
  • OPENJSON can deal with (almost) unlimited counts
  • OPENJSON demands for v2016
  • Everybody is waiting for STRING_SPLIT with the position

UPDATE Added STRING_SPLIT to the test

In the meanwhile I re-run the test with two more test sections using STRING_SPLIT(). As position I had to return a hardcoded value as this function does not return the parts index.

In all tested cases OPENJSON was close with STRING_SPLIT and often faster:

5 items in 1000 rows

250 delimited8K with NVARCHAR(4000)
124 delimited8K with VARCHAR(8000) --this function is best with many rows in VARCHAR
203 OPENJSON with NVARCHAR(4000)
204 OPENJSON with VARCHAR(8000)
235 STRING_SPLIT with NVARCHAR(4000)
234 STRING_SPLIT with VARCHAR(8000)

200 items in 30 rows

140 delimited8K with NVARCHAR(4000)
31  delimited8K with VARCHAR(8000)
47  OPENJSON with NVARCHAR(4000)
31  OPENJSON with VARCHAR(8000)
47  STRING_SPLIT with NVARCHAR(4000)
31  STRING_SPLIT with VARCHAR(8000)

100 items in 10.000 rows

8145    delimited8K with NVARCHAR(4000)
2806    delimited8K with VARCHAR(8000) --fast with many rows!
5112    OPENJSON with NVARCHAR(4000)
4501    OPENJSON with VARCHAR(8000)
5028    STRING_SPLIT with NVARCHAR(4000)
5126    STRING_SPLIT with VARCHAR(8000)

The simple answer is, no. Microsoft so far have refused to provide Ordinal position as part of the return dataset in STRING_SPLIT. Youll need to use a different solution Im afraid. For example Jeff Modens DelimitedSplit8k.

(Yes, I realise this is more or less a link only answer, however, pasting Jeffs solution here would effectively be plagiarism).

If you were to use Jeffs solution, then you would be able to do something like:

SELECT *
FROM dbo.DelimitedSplit8K(a,b,c,d,e,f,g,h,i,j,k,,) DS
WHERE ItemNumber = 2;

Of course, youd likely be passing column rather than a literal string.

return value at a position from STRING_SPLIT in SQL Server 2016

I just extended @Shnugos answer if the splitted text would contain line breaks, unicode and other non json compatible characters, to use
STRING_ESCAPE

My Test code with pipe as separator instead comma:

DECLARE @Separator VARCHAR(5) = STRING_ESCAPE(|, json); -- here pipe or use any other separator (even ones escaped by json)
DECLARE @LongText VARCHAR(MAX) = Albert says: baby, listen!|ve Çağrı söylüyor: Elma|1st Line + CHAR(13) + CHAR(10) + 2nd line;

SELECT * FROM OPENJSON([ +  REPLACE(STRING_ESCAPE(@LongText, json), @Separator ,,) + ]); -- ok
-- SELECT * FROM OPENJSON([ +  REPLACE(@LongText, @Separator ,,) + ]); -- fails with: JSON text is not properly formatted. ...

Updated due to comment from Simon Zeinstra

Leave a Reply

Your email address will not be published. Required fields are marked *