sql-server - 使用返回 null 的 PARSENAME 进行分隔 - SQL Server

Question

我正在尝试使用PARSENAME为某些行返回空值的列来分隔。这是样本数据。

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 和 2 显示正确的结果。Sku 3 和 4 没有显示任何值（一直为空）。请协助。

标签： sql-serverparsingsql-server-2012

Answer 1

只是通过 XML 的另一种选择。易于扩展，图案非常清晰。

例子

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

退货