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`.
```
CREATE OR ALTER FUNCTION dbo.SplitStringLOBjson
(
@input nvarchar(max),
@delimiter nchar(1) = N','
)
RETURNS table
WITH SCHEMABINDING
AS
RETURN
SELECT
ordinal = TRY_CONVERT(bigint, OJ.[Key]),
element = OJ.[Value]
FROM OPENJSON
(
CONCAT
(
N'["',
REPLACE
(
STRING_ESCAPE(@input, N'json')
COLLATE Latin1_General_BIN2,
@delimiter,
N'","'
),
N'"]'
)
) AS OJ;
```
db<>fiddle:
<>https://dbfiddle.uk/?rdbms=sqlserver_2016&fiddle=fdb7fca0e2d2c232afd527fa59783e71