Delimit using PARSENAME returning null - SQL Server

Question

I am trying to delimit a column using PARSENAME which is returning null values for some rows. Here is the sample data.

CREATE TABLE Table1
(
     [sku] varchar(50), 
     [nameslist] varchar(max)
);

INSERT INTO Table1 ([sku], [nameslist])
VALUES ('1', '1991|1994|Freightliner|FLD112'),
       ('2', '1983|1993|Chevrolet|S10 Blazer'),
       ('3', '1993|1993|Freightliner|FLC112|FLC11264S'),
       ('4', '1987|1987|GMC|S15|Base|EL|Gypsy|High Sierra|Sierra Classic');

WITH CTE AS
(
    SELECT  
        sku, nameslist,
        LEN(nameslist) - LEN(REPLACE(nameslist, '|', '')) N
    FROM 
        Table1
)
SELECT  
    sku, nameslist,
    PARSENAME(REPLACE(nameslist, '|', '.'), N + 1) AS year1, 
    PARSENAME(REPLACE(nameslist, '|', '.'), N) AS year2,
    PARSENAME(REPLACE(nameslist, '|', '.'), N - 1) AS make, 
    PARSENAME(REPLACE(nameslist, '|', '.'), N - 2) AS model, 
    PARSENAME(REPLACE(nameslist, '|', '.'), N - 3) AS submo,
    PARSENAME(REPLACE(nameslist, '|', '.'), N - 4) AS submo2,
    PARSENAME(REPLACE(nameslist, '|', '.'), N - 5) AS submo3,
    PARSENAME(REPLACE(nameslist, '|', '.'), N - 6) AS futureuse1,
    PARSENAME(REPLACE(nameslist, '|', '.'), N - 7) AS futureuse2,
    PARSENAME(REPLACE(nameslist, '|', '.'), N - 8) AS futureuse3,
    PARSENAME(REPLACE(nameslist, '|', '.'), N - 9) AS futureuse4,
    PARSENAME(REPLACE(nameslist, '|', '.'), N + 10) AS futureuse5
FROM 
    CTE;

DROP TABLE table1;

sku 1 and 2 shows proper results. Sku 3 and 4 are not showing any value (are null all the way). Please assist.

Answer 1

Just another option via XML. Easy to expand, the pattern is very clear.

Example

Select sku
      ,B.*
 From  TABLE1 A
 Cross Apply (
    Select Pos1 = ltrim(rtrim(xDim.value('/x[1]','varchar(max)')))
          ,Pos2 = ltrim(rtrim(xDim.value('/x[2]','varchar(max)')))
          ,Pos3 = ltrim(rtrim(xDim.value('/x[3]','varchar(max)')))
          ,Pos4 = ltrim(rtrim(xDim.value('/x[4]','varchar(max)')))
          ,Pos5 = ltrim(rtrim(xDim.value('/x[5]','varchar(max)')))
          ,Pos6 = ltrim(rtrim(xDim.value('/x[6]','varchar(max)')))
          ,Pos7 = ltrim(rtrim(xDim.value('/x[7]','varchar(max)')))
          ,Pos8 = ltrim(rtrim(xDim.value('/x[8]','varchar(max)')))
          ,Pos9 = ltrim(rtrim(xDim.value('/x[9]','varchar(max)')))
       From  (Select Cast('<x>' + replace((Select replace([nameslist],'|','§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml) as xDim) as A 
) B

Returns