How can I split a single, very long string into separate columns and rows delimited by commas and semicolons in SQL Server?

I'm trying to split a single very long string into a separate columns and rows.

The columns need to be delimited with the , character, and the rows are delimited with the ; character.

Example of data that's stored in the SQL Server table in as datatype Nvarchar(max)

Containing 11 columns, and hundreds of rows. PARSENAME does not appear to work because of limitations and how it likes to use full stops which is a problem because there are GPS co-ordinates in the data.

Split string does work to move the data into a new row though but I was getting a lot of NULLs because of parse name limitation. Please advise... I've googled and chatgpted this for like a day with no great results.

Data example First 5 rows but could be up to thousands of rows.

"Tag No,Tag2 No,Location ID,Log Length,Log Diameter,Log Thick Diameter,Volume,Team ID,Scan DateTime,Checker ID,LogType ID,Latitude,Longitude,Altitude,HCS No;67599715,0,155341,330,17,0,0.0901491,144,2022-11-16 13:59:12.208,1008,HEWSAW,0,0,0,83493;67599716,0,155341,330,15,0,0.0718509,144,2022-11-16 13:59:16.997,1008,HEWSAW,0,0,0,83493;67599717,0,155341,330,16,0,0.0807408,144,2022-11-16 13:59:20.733,1008,HEWSAW,0,0,0,83493;67599718,0,155341,330,18,0,0.100076,144,2022-11-16 13:59:23.618,1008,HEWSAW,0,0,0,83493;67599719,0,155341,330,17,0,0.0901491,144,2022-11-16 13:59:26.736,1008,HEWSAW,0,0,0,83493;67599720,0,155341,330,15,0,0.0718509,144,2022-11-16 13:59:29.718,1008,HEWSAW,0,0,0,83493;67599721,0,155341,330,16,0,0.0807408,144,2022-11-16 13:59:33.809,1008,HEWSAW,0,0,0,83493;67599722,0,155341,330,17,0,0.0901491,144,2022-11-16 13:59:38.118,1008,HEWSAW,0,0,0,83493;67599723,0,155341,330,15,0,0.0718509,144,2022-11-16 13:59:41.346,1008,HEWSAW,0,0,0,83493;"

CREATE TABLE #test124 
(
    LoadedTag1 varchar(15),
    LoadCheckerID VARCHAR(50),
    LoadedAt varchar(50)
);

INSERT INTO #test124 (LoadedTag1, LoadCheckerID, LoadedAt)
    SELECT
        PARSENAME(REPLACE(VALUE, ',', '.'), 11) AS LoadedTag1,
        PARSENAME(REPLACE(VALUE, ',', '.'), 2) AS LoadCheckerID,
        PARSENAME(REPLACE(VALUE, ',', '.'), 1) AS LoadedAt
    FROM 
        STRING_SPLIT(("Pauls Data"), ';');


SELECT * FROM #test124

DROP TABLE #test124

Then I tried this approach and now all I have is one column, where I want row and column

CREATE TABLE Testdata
(
    TagNo Int,
    Tag2No int, 
    LocationID nvarchar (50),
    LogLength int,
    LogDiameter int, 
    LogThickDiameter int,
    Volume float,
    TeamID id,
    ScanDateTime datetime,
    CheckerID nvarchar (50),
    LogTypeID nvarchar(50),
    Latitude float,
    Longitude float, 
    Altitude float
   
);
 
 String VARCHAR(MAX) = (select LogString from syncbuffer where id = '522900')



WITH tmp(TagNo,
Tag2No,
LocationID,
LogLength,
LogDiameter,
LogThickDiameter,
Volume,
TeamID,
ScanDateTime,
CheckerID,
LogTypeID,
Latitude,
Longitude,
Altitude, DataItem, String) AS
(
    SELECT
        TagNo,
Tag2No,
LocationID,
LogLength,
LogDiameter,
LogThickDiameter,
Volume,
TeamID,
ScanDateTime,
CheckerID,
LogTypeID,
Latitude,
Longitude
Altitude,
        LEFT(String, CHARINDEX(',', String + ',') - 1),
        STUFF(String, 1, CHARINDEX(',', String + ','), '')
    FROM Testdata
    UNION all

    SELECT
             TagNo,
Tag2No,
LocationID,
LogLength,
LogDiameter,
LogThickDiameter,
Volume,
TeamID,
ScanDateTime,
CheckerID,
LogTypeID,
Latitude,
Longitude
Altitude,
        LEFT(String, CHARINDEX(',', String + ',') - 1),
        STUFF(String, 1, CHARINDEX(',', String + ','), '')
    FROM tmp
    WHERE
        String > ''
)
SELECT
         TagNo,
Tag2No,
LocationID,
LogLength,
LogDiameter,
LogThickDiameter,
Volume,
TeamID,
ScanDateTime,
CheckerID,
LogTypeID,
Latitude,
Longitude
Altitude
FROM tmp
ORDER BY SomeID;
-- OPTION (maxrecursion 0)
-- normally recursion is limited to 100. If you know you have very long
-- strings, uncomment the option

Please try the following solution.

It will work starting from SQL Server 2017 onwards.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, LogString NVARCHAR(MAX));
INSERT @tbl (LogString) VALUES
(N'Tag No,Tag2 No,Location ID,Log Length,Log Diameter,Log Thick Diameter,Volume,Team ID,Scan DateTime,Checker ID,LogType ID,Latitude,Longitude,Altitude,HCS No;67599715,0,155341,330,17,0,0.0901491,144,2022-11-16 13:59:12.208,1008,HEWSAW,0,0,0,83493;67599716,0,155341,330,15,0,0.0718509,144,2022-11-16 13:59:16.997,1008,HEWSAW,0,0,0,83493;67599717,0,155341,330,16,0,0.0807408,144,2022-11-16 13:59:20.733,1008,HEWSAW,0,0,0,83493;67599718,0,155341,330,18,0,0.100076,144,2022-11-16 13:59:23.618,1008,HEWSAW,0,0,0,83493;67599719,0,155341,330,17,0,0.0901491,144,2022-11-16 13:59:26.736,1008,HEWSAW,0,0,0,83493;67599720,0,155341,330,15,0,0.0718509,144,2022-11-16 13:59:29.718,1008,HEWSAW,0,0,0,83493;67599721,0,155341,330,16,0,0.0807408,144,2022-11-16 13:59:33.809,1008,HEWSAW,0,0,0,83493;67599722,0,155341,330,17,0,0.0901491,144,2022-11-16 13:59:38.118,1008,HEWSAW,0,0,0,83493;67599723,0,155341,330,15,0,0.0718509,144,2022-11-16 13:59:41.346,1008,HEWSAW,0,0,0,83493;');
-- DDL and sample data population, end

SELECT ID
    , TagNo = JSON_VALUE(j,'$[0]')
    , Tag2No = JSON_VALUE(j,'$[1]')
    , LocationID = JSON_VALUE(j,'$[2]')
    , LogLength = JSON_VALUE(j,'$[3]')
    ...
FROM @tbl
    CROSS APPLY STRING_SPLIT(TRIM(';' FROM LogString), ';')
    CROSS APPLY (SELECT '["' + REPLACE(STRING_ESCAPE(value, 'json'), ',', '","') + '"]') AS t1(j)
WHERE value NOT LIKE 'Tag%';

Output