Split your string using a new tool

DROP FUNCTION IF EXISTS dbo.STRING_SPLIT;
GO
— Returns NVARCHAR(MAX)
CREATE OR ALTER FUNCTION dbo.STRING_SPLIT
(
@Data NVARCHAR(MAX),
@Delimiter NVARCHAR(MAX) = N’,’
)
RETURNS TABLE
RETURN SELECT CAST(x.[Key] AS INT) AS Position,
y.Value
FROM (
VALUES (N'[{“p”:”‘ + REPLACE(@Data, @Delimiter, N'”},{“p”:”‘) + N'”}]’)
) AS d(Data)
CROSS APPLY OPENJSON(d.Data, N’$’) AS x
CROSS APPLY OPENJSON(x.Value, N’$’)
WITH (
Value NVARCHAR(MAX) N’$.p’
) AS y
GO
SELECT *
FROM dbo.STRING_SPLIT(N’Ananas,Banan,Citron’, ‘,’);
GO
— Returns NVARCHAR(4000)
CREATE OR ALTER FUNCTION dbo.STRING_SPLIT
(
@Data NVARCHAR(MAX),
@Delimiter NVARCHAR(MAX) = N’,’
)
RETURNS TABLE
RETURN SELECT CAST(x.[Key] AS INT) AS Position,
JSON_VALUE(x.Value, N’$.p’) AS Value
FROM (
VALUES (N'[{“p”:”‘ + REPLACE(@Data, @Delimiter, N'”},{“p”:”‘) + N'”}]’)
) AS d(Data)
CROSS APPLY OPENJSON(d.Data, N’$’) AS x;
GO
SELECT *
FROM dbo.STRING_SPLIT(N’Ananas,Banan,Citron’, ‘,’);