使用多个分隔符提取数据？

只要遗留数据如所述并且没有任何奇怪的变化，以下应该可以工作，因为它会产生所需的输出。

笔记：

• 以下两个代码部分应在 SSMS 中一起运行，因为它们使用表变量，因此需要在同一个查询批处理中。我只是将它分成两部分，以便更容易专注于主要查询。
• 我使用的拆分器String_Split是基于 SQLCLR 的，可在SQL#库中使用（我是该库的作者，但String_Split和其他工具在免费版本中可用）。但是，任何返回行/项目 # 的拆分器都应该同样工作（只是不要使用基于WHILE循环的拆分器）。

测试设置：

DECLARE @SampleData TABLE (ID INT NOT NULL, SourceStuff VARCHAR(MAX) NOT NULL);

INSERT INTO @SampleData (ID, SourceStuff) VALUES (62963, 'RENZ;MICHAEL;DECEASED,WANDER;MARIA;MINOR,WANDER;HENRY RUDOLPH;MINOR,WANDER;ROSA;MINOR,WANDER;PAUL EMIL;MINOR');
INSERT INTO @SampleData (ID, SourceStuff) VALUES (62964, 'HERNDON;A C;ESTATE,BERRING;A F;DECEASED,BEIRING;A F;DECEASED,BEIRING;ANDREAS FREDERICK;DECEASED');
INSERT INTO @SampleData (ID, SourceStuff) VALUES (62965, 'ZINCH;;ESTATE,ZINTZ;;ESTATE,HAYNES;HENRY;DECEASED');
INSERT INTO @SampleData (ID, SourceStuff) VALUES (62966, 'KRAUS;JOSEPHINE;MINOR,KENNEDY;GEORGE;DECEASED');
INSERT INTO @SampleData (ID, SourceStuff) VALUES (62967, 'CAREY;JAMES;ESTATE,DE LA GARZA;REFUGIO;DECEASED');
INSERT INTO @SampleData (ID, SourceStuff) VALUES (62968, 'LEWIS;FLORENCE;ESTATE,LOCKWOOD;ALBERT A;DECEASED');
INSERT INTO @SampleData (ID, SourceStuff) VALUES (62969, 'GLAESER;EMMA;MINOR,GLAESER;HERMAN JR;MINOR,GLAESER;HERMAN;MINOR,RODRIGUEZ;HILARIO;DECEASED,RODRIGUEZ;MARIE;DECEASED');
INSERT INTO @SampleData (ID, SourceStuff) VALUES (62970, 'STORY;BETTIE;ESTATE,EIGENDORFF;FRANZ;DECEASED');
INSERT INTO @SampleData (ID, SourceStuff) VALUES (62971, 'HOWELL;MAMIE;MINOR,HOWELL;ETHEL;MINOR');

主要查询：

;WITH cte AS
(
  SELECT sd.ID,
         split.SplitNum,
         split.SplitVal,
         CHARINDEX(N';', split.SplitVal) AS [FirstDelimeter],
         CHARINDEX(N';', split.SplitVal,
                         (CHARINDEX(N';', split.SplitVal) + 1)) AS [SecondDelimeter]
  FROM   #SampleData sd
  CROSS APPLY SQL#.String_Split(sd.SourceStuff, N',', 1) split
)
SELECT tmp.ID,
       tmp.SplitNum AS [Sequence],
       SUBSTRING(tmp.SplitVal,
                 1,
                 (tmp.FirstDelimeter - 1)) AS [Last],
       SUBSTRING(tmp.SplitVal,
                 (tmp.FirstDelimeter + 1),
                 (tmp.SecondDelimeter - (tmp.FirstDelimeter + 1))) AS [FirstMiddle],
       SUBSTRING(tmp.SplitVal,
                 (tmp.SecondDelimeter + 1),
                 LEN(tmp.SplitVal)) AS [Type]
FROM   cte tmp;

我对这种事情使用流式表值函数：

select b.A as FirstName
     , b.C as LastName
     , Cast(b.Q as date) as DateOfBirth
from <FileTable> as a
cross apply dbo.ReadDelimited(
    a.file_stream
  , @delimitChars
  , @escapeChars
  , @newLineChars
  , @encoding
  , default
  , default
) as b
where a.Name = @fileName

ReadDelimited 将返回 A:CZ 列的 Excel 样式结果集。然后我们select只使用 A、C 和 Q 列，并使用标准 T-SQL 添加正确的类型和别名。如果您的数据包含标题，那么您可以使用Row_Number(). 该示例还演示了如何轻松地将 STVF 与 Sql Server 的 FileTables 功能结合起来。

在您的情况下，您需要设置@delimitChars = ';'and @newLineChars = ','。

C# CLR 代码：

using Microsoft.SqlServer.Server;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data.SqlTypes;
using System.IO;
using System.Text;

public partial class ReadDelimitedFunctions
{
    private const int MAX_COLUMN_COUNT = 26 * 4;

    [SqlFunction(
        DataAccess = DataAccessKind.None,
        FillRowMethodName = "ReadDelimited_FillRow",
        IsDeterministic = true,
        IsPrecise = true,
        SystemDataAccess = SystemDataAccessKind.None,
        TableDefinition =
            @"A nvarchar(4000), B nvarchar(4000), C nvarchar(4000), D nvarchar(4000), E nvarchar(4000), F nvarchar(4000)
            , G nvarchar(4000), H nvarchar(4000), I nvarchar(4000), J nvarchar(4000), K nvarchar(4000), L nvarchar(4000)
            , M nvarchar(4000), N nvarchar(4000), O nvarchar(4000), P nvarchar(4000), Q nvarchar(4000), R nvarchar(4000)
            , S nvarchar(4000), T nvarchar(4000), U nvarchar(4000), V nvarchar(4000), W nvarchar(4000), X nvarchar(4000)
            , Y nvarchar(4000), Z nvarchar(4000), AA nvarchar(4000), AB nvarchar(4000), AC nvarchar(4000), AD nvarchar(4000)
            , AE nvarchar(4000), AF nvarchar(4000), AG nvarchar(4000), AH nvarchar(4000), AI nvarchar(4000), AJ nvarchar(4000)
            , AK nvarchar(4000), AL nvarchar(4000), AM nvarchar(4000), AN nvarchar(4000), AO nvarchar(4000), AP nvarchar(4000)
            , AQ nvarchar(4000), AR nvarchar(4000), [AS] nvarchar(4000), AT nvarchar(4000), AU nvarchar(4000), AV nvarchar(4000)
            , AW nvarchar(4000), AX nvarchar(4000), AY nvarchar(4000), AZ nvarchar(4000), BA nvarchar(4000), BB nvarchar(4000)
            , BC nvarchar(4000), BD nvarchar(4000), BE nvarchar(4000), BF nvarchar(4000), BG nvarchar(4000), BH nvarchar(4000)
            , BI nvarchar(4000), BJ nvarchar(4000), BK nvarchar(4000), BL nvarchar(4000), BM nvarchar(4000), BN nvarchar(4000)
            , BO nvarchar(4000), BP nvarchar(4000), BQ nvarchar(4000), BR nvarchar(4000), BS nvarchar(4000), BT nvarchar(4000)
            , BU nvarchar(4000), BV nvarchar(4000), BW nvarchar(4000), BX nvarchar(4000), [BY] nvarchar(4000), BZ nvarchar(4000)
            , CA nvarchar(4000), CB nvarchar(4000), CC nvarchar(4000), CD nvarchar(4000), CE nvarchar(4000), CF nvarchar(4000)
            , CG nvarchar(4000), CH nvarchar(4000), CI nvarchar(4000), CJ nvarchar(4000), CK nvarchar(4000), CL nvarchar(4000)
            , CM nvarchar(4000), CN nvarchar(4000), CO nvarchar(4000), CP nvarchar(4000), CQ nvarchar(4000), CR nvarchar(4000)
            , CS nvarchar(4000), CT nvarchar(4000), CU nvarchar(4000), CV nvarchar(4000), CW nvarchar(4000), CX nvarchar(4000)
            , CY nvarchar(4000), CZ nvarchar(4000)"
    )]
    public static IEnumerable ReadDelimited(SqlBytes bytes, SqlChars delimitChars, SqlChars escapeChars, SqlChars newLineChars, SqlChars encodingName, SqlInt32 columnCount, SqlInt32 bufferSize) {
        if (delimitChars.IsNull) { delimitChars = new SqlChars(","); }
        if (escapeChars.IsNull) { escapeChars = new SqlChars("\""); }
        if (newLineChars.IsNull) { newLineChars = new SqlChars("\n"); }
        if (encodingName.IsNull) { encodingName = new SqlChars("utf-8"); }
        if (columnCount.IsNull) { columnCount = MAX_COLUMN_COUNT; }
        else if (columnCount.Value > MAX_COLUMN_COUNT) { throw new IndexOutOfRangeException(string.Format("ReadDelimited supports a maximum of {0} columns.", MAX_COLUMN_COUNT)); }
        if (bufferSize.IsNull) { bufferSize = 4096; }

        return ReadDelimited_Parse(bytes.Stream, delimitChars.Value, escapeChars.Value, newLineChars.Value, new string(encodingName.Value), columnCount.Value, bufferSize.Value);
    }

    private static IEnumerable<SqlChars[]> ReadDelimited_Parse(Stream stream, char[] delimitChars, char[] escapeChars, char[] newLineChars, string encodingName, int columnCount, int bufferSize = 4096) {
        var encoding = Encoding.GetEncoding(encodingName);
        var builder = new StringBuilder();
        var rawBuffer = new byte[bufferSize];
        var charBuffer = new char[bufferSize];
        var result = new SqlChars[MAX_COLUMN_COUNT];
        var escaping = false;
        var escapeIndex = 0;
        var delimitIndex = 0;
        var columnIndex = 0;
        var rowIndex = 0;

        while (stream.Read(rawBuffer, 0, bufferSize) > 0) { // while there are bytes to read...
            encoding.GetChars(rawBuffer, 0, bufferSize, charBuffer, 0); // decode rawBuffer into charBuffer
            Array.Clear(rawBuffer, 0, bufferSize); // reset rawBuffer

            for (var i = 0; i < bufferSize; i++) { // process each character in the buffer
                builder.Append(charBuffer[i]); // capture the current character in the builder

                if (charBuffer[i] == escapeChars[escapeIndex] && (++escapeIndex == escapeChars.Length)) { // toggle escaping
                    escapeIndex = 0;
                    escaping = !escaping;
                    builder.Remove(builder.Length - escapeChars.Length, escapeChars.Length);
                }

                if (!escaping) {
                    if (columnIndex < columnCount && charBuffer[i] == delimitChars[delimitIndex] && (++delimitIndex == delimitChars.Length)) { // process columns
                        delimitIndex = 0;
                        result[columnIndex++] = new SqlChars(builder.ToString(0, builder.Length - delimitChars.Length));
                        builder.Clear();
                    }

                    if (charBuffer[i] == newLineChars[rowIndex] && (++rowIndex == newLineChars.Length)) { // process rows
                        if (columnIndex < columnCount) { // process last column
                            result[columnIndex++] = new SqlChars(builder.ToString(0, builder.Length - newLineChars.Length));
                        }

                        while (columnIndex < MAX_COLUMN_COUNT) { // null-extend columns
                            result[columnIndex++] = SqlChars.Null;
                        }

                        yield return result; // return current row

                        // reset variables for next row
                        builder.Clear();
                        rowIndex = 0;
                        columnIndex = 0;
                        Array.Clear(result, 0, MAX_COLUMN_COUNT);
                    }
                }
            }
        }

        // process last row
        var s = builder.ToString();

        result[columnIndex++] = string.IsNullOrEmpty(s) ? SqlChars.Null : new SqlChars(s); // process last column

        if (result[1] != null) { // skip last row if empty
            while (columnIndex < MAX_COLUMN_COUNT) { // null-extend columns
                result[columnIndex++] = SqlChars.Null;
            }

            yield return result;
        }
    }

    private static void ReadDelimited_FillRow(object obj
        , out SqlChars A, out SqlChars B, out SqlChars C, out SqlChars D, out SqlChars E, out SqlChars F
        , out SqlChars G, out SqlChars H, out SqlChars I, out SqlChars J, out SqlChars K, out SqlChars L
        , out SqlChars M, out SqlChars N, out SqlChars O, out SqlChars P, out SqlChars Q, out SqlChars R
        , out SqlChars S, out SqlChars T, out SqlChars U, out SqlChars V, out SqlChars W, out SqlChars X
        , out SqlChars Y, out SqlChars Z, out SqlChars AA, out SqlChars AB, out SqlChars AC, out SqlChars AD
        , out SqlChars AE, out SqlChars AF, out SqlChars AG, out SqlChars AH, out SqlChars AI, out SqlChars AJ
        , out SqlChars AK, out SqlChars AL, out SqlChars AM, out SqlChars AN, out SqlChars AO, out SqlChars AP
        , out SqlChars AQ, out SqlChars AR, out SqlChars AS, out SqlChars AT, out SqlChars AU, out SqlChars AV
        , out SqlChars AW, out SqlChars AX, out SqlChars AY, out SqlChars AZ, out SqlChars BA, out SqlChars BB
        , out SqlChars BC, out SqlChars BD, out SqlChars BE, out SqlChars BF, out SqlChars BG, out SqlChars BH
        , out SqlChars BI, out SqlChars BJ, out SqlChars BK, out SqlChars BL, out SqlChars BM, out SqlChars BN
        , out SqlChars BO, out SqlChars BP, out SqlChars BQ, out SqlChars BR, out SqlChars BS, out SqlChars BT
        , out SqlChars BU, out SqlChars BV, out SqlChars BW, out SqlChars BX, out SqlChars BY, out SqlChars BZ
        , out SqlChars CA, out SqlChars CB, out SqlChars CC, out SqlChars CD, out SqlChars CE, out SqlChars CF
        , out SqlChars CG, out SqlChars CH, out SqlChars CI, out SqlChars CJ, out SqlChars CK, out SqlChars CL
        , out SqlChars CM, out SqlChars CN, out SqlChars CO, out SqlChars CP, out SqlChars CQ, out SqlChars CR
        , out SqlChars CS, out SqlChars CT, out SqlChars CU, out SqlChars CV, out SqlChars CW, out SqlChars CX
        , out SqlChars CY, out SqlChars CZ
    ) {
        var fields = (SqlChars[])obj;

        A = fields[0]; B = fields[1]; C = fields[2]; D = fields[3]; E = fields[4]; F = fields[5];
        G = fields[6]; H = fields[7]; I = fields[8]; J = fields[9]; K = fields[10]; L = fields[11];
        M = fields[12]; N = fields[13]; O = fields[14]; P = fields[15]; Q = fields[16]; R = fields[17];
        S = fields[18]; T = fields[19]; U = fields[20]; V = fields[21]; W = fields[22]; X = fields[23];
        Y = fields[24]; Z = fields[25]; AA = fields[26]; AB = fields[27]; AC = fields[28]; AD = fields[29];
        AE = fields[30]; AF = fields[31]; AG = fields[32]; AH = fields[33]; AI = fields[34]; AJ = fields[35];
        AK = fields[36]; AL = fields[37]; AM = fields[38]; AN = fields[39]; AO = fields[40]; AP = fields[41];
        AQ = fields[42]; AR = fields[43]; AS = fields[44]; AT = fields[45]; AU = fields[46]; AV = fields[47];
        AW = fields[48]; AX = fields[49]; AY = fields[50]; AZ = fields[51]; BA = fields[52]; BB = fields[53];
        BC = fields[54]; BD = fields[55]; BE = fields[56]; BF = fields[57]; BG = fields[58]; BH = fields[59];
        BI = fields[60]; BJ = fields[61]; BK = fields[62]; BL = fields[63]; BM = fields[64]; BN = fields[65];
        BO = fields[66]; BP = fields[67]; BQ = fields[68]; BR = fields[69]; BS = fields[70]; BT = fields[71];
        BU = fields[72]; BV = fields[73]; BW = fields[74]; BX = fields[75]; BY = fields[76]; BZ = fields[77];
        CA = fields[78]; CB = fields[79]; CC = fields[80]; CD = fields[81]; CE = fields[82]; CF = fields[83];
        CG = fields[84]; CH = fields[85]; CI = fields[86]; CJ = fields[87]; CK = fields[88]; CL = fields[89];
        CM = fields[90]; CN = fields[91]; CO = fields[92]; CP = fields[93]; CQ = fields[94]; CR = fields[95];
        CS = fields[96]; CT = fields[97]; CU = fields[98]; CV = fields[99]; CW = fields[100]; CX = fields[101];
        CY = fields[102]; CZ = fields[103];
    }
}