Parse Rows Into 2 Columns Licence# and Item #

I have some data in the database that is stored comma separated. I need to parse it so that I have one too many. The data looks like the following. From the query I've created, I have gotten this far but become stuck as I go back and forth and cannot figure out what I'm doing wrong here.

Results:

Expected

    DROP TABLE IF EXISTS testdata;
    CREATE TABLE testdata (
        LicenseNumberList VARCHAR(100),
        ItemNumberList VARCHAR(100)
    );

    -- Insert data
    INSERT INTO testdata (LicenseNumberList, ItemNumberList)
    VALUES ('[1234],[8854],[6987]', '[4555,8777,4444],[4415],[4444]');

DROP FUNCTION IF EXISTS dbo.SplitString
GO
-- Create a split function
CREATE FUNCTION dbo.SplitString
(
    @String VARCHAR(MAX),
    @Delimiter CHAR(1)
)
RETURNS @Result TABLE (Value VARCHAR(MAX))
AS
BEGIN
    DECLARE @Value VARCHAR(MAX)
    
    WHILE CHARINDEX(@Delimiter, @String) > 0
    BEGIN
        SET @Value = SUBSTRING(@String, 1, CHARINDEX(@Delimiter, @String) - 1)
        INSERT INTO @Result (Value) VALUES (@Value)
        SET @String = SUBSTRING(@String, CHARINDEX(@Delimiter, @String) + 1, LEN(@String))
    END
    
    IF LEN(@String) > 0
        INSERT INTO @Result (Value) VALUES (@String)
    
    RETURN
END;
GO

-- Split the license numbers and item numbers into separate rows
WITH LicenseNumbersParsed AS (
    SELECT
        LTRIM(RTRIM(REPLACE(REPLACE(Value, '[', ''), ']', ''))) AS LicenseNumber,
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RowNumber
    FROM
        testdata
    CROSS APPLY dbo.SplitString(LicenseNumberList, ',')
), ItemNumbersParsed AS (
    SELECT
        ln.RowNumber,
        LTRIM(RTRIM(REPLACE(REPLACE(Value, '[', ''), ']', ''))) AS ItemNumber,
        ROW_NUMBER() OVER (PARTITION BY ln.RowNumber ORDER BY (SELECT NULL)) AS ItemRowNumber
    FROM
        testdata
    CROSS APPLY dbo.SplitString(ItemNumberList, ',')
    JOIN LicenseNumbersParsed ln ON 1 = 1
)
SELECT
    ln.LicenseNumber,
    STRING_AGG(ip.ItemNumber, ',') WITHIN GROUP (ORDER BY ip.ItemRowNumber) AS ItemNumberList
FROM
    LicenseNumbersParsed ln
JOIN ItemNumbersParsed ip ON ln.RowNumber = ip.RowNumber
GROUP BY
    ln.LicenseNumber
ORDER BY
    ln.LicenseNumber;

Please try the following solution. It will work starting from SQL Server 2017 onwards.

It is based on the @lptr idea.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (LicenseNumberList VARCHAR(100), ItemNumberList VARCHAR(100));
INSERT INTO @tbl (LicenseNumberList, ItemNumberList)
VALUES ('[1234],[8854],[6987]', '[4555,8777,4444],[4415],[4444]');
-- DDL and sample data population, end

SELECT LicenseNumber = JSON_VALUE(l.value, '$[0]')
    , ItemNumber = TRIM('[]' FROM JSON_QUERY('['+t.ItemNumberList+']', '$['+l.[key] COLLATE DATABASE_DEFAULT +']'))
FROM @tbl AS t 
    CROSS APPLY OPENJSON('['+t.LicenseNumberList+']') AS l;

Output