LOB string splitter with ordinals in SQL Server 2016 - Databases

TopAnswers Databases

Meta

Databases

TeX

Code Golf

APL

C

C++

C#

.net

db<>fiddle

F#

Java

*nix

PHP

PowerShell

Python

Rust

टेक्-मराठी

Typst

Web Client Dev

Web Server Dev

LOB string splitter with ordinals in SQL Server 2016

sql-server sql-server-2016 add tag

Paul White

Is there a non-CLR string splitter for SQL Server 2016 that performs well for very large input strings and returns a reliable ordinal column like `STRING_SPLIT` can on SQL Server 2022 and Azure?

Top Answer

Paul White

This inline TVF converts the input string to JSON array format, then uses default `OPENJSON`.

db<>fiddle:

<>https://dbfiddle.uk/?rdbms=sqlserver_2016&fiddle=fdb7fca0e2d2c232afd527fa59783e71

comments

transcript

Paul White

no worries

Andriy M replying to Paul White

Missed both the "show hidden batches" divider and the "hide" parameter in the link. Should've paid more attention, sorry. And thanks for making it clearer.

Paul White

I edited the answer to remove the *hide* parameter.

Paul White

Or just look at the fiddle without the *hide* parameter https://dbfiddle.uk/8_eRpErM

Paul White replying to Andriy M

Something about the db<>fiddle changes messed up the batch hiding feature. Show the hidden batch at the end to see the comparison. The original hid the sample data loading batch, as I recall

Andriy M

Nice idea anyway.  Too bad one of our main servers is still 2014.  Not for long, I hope

Andriy M

Is `CROSS APPLY STRING_SPLIT` in the fiddle's last batch supposed to be `CROSS APPLY dbo.SplitStringLOBjson`?

Paul White replying to Joe Obbish

Ah the `REPLACE` was incorrect. I only tested it with commas. Fixed and simplified now.

Joe Obbish

Does this only work with a comma as the delimiter? I'm getting an error using different delimiters. Example: `SELECT * FROM dbo.SplitStringLOBjson(N'a|b', N'|')`

1 Answer