3

I have a legacy data source column that is delimitted by semicolons and commas. The first semicolon indicates the last name, the second indicates the first and middle name (or initials), and the last semicolon indicates the type of individual. The comma indicates that a new name has began. Here is a sample of this data.

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

I am attempting to pull the data in a manner such as this so that it can be adapted to a different schema:

+-----------+------------+-------------+-------------------+----------+
|      ID   |   SEQUENCE |    LAST     |    FIRSTMIDDLE    |   TYPE   |
+-----------+------------+-------------+-------------------+----------+
|     62963 |          1 | RENZ        | MICHAEL           | DECEASED |
|     62963 |          2 | WANDER      | MARIA             | MINOR    |
|     62963 |          3 | WANDER      | HENRY RUDOLPH     | MINOR    |
|     62963 |          4 | WANDER      | ROSA              | MINOR    |
|     62963 |          5 | WANDER      | PAUL EMIL         | MINOR    |
|     62964 |          1 | HERNDON     | A C               | ESTATE   |
|     62964 |          2 | BERRING     | A F               | DECEASED |
|     62964 |          3 | BEIRING     | A F               | DECEASED |
|     62964 |          4 | BEIRING     | ANDREAS FREDERICK | DECEASED |
|     62965 |          1 | ZINCH       |                   | ESTATE   |
|     62965 |          2 | ZINTZ       |                   | ESTATE   |
|     62965 |          3 | HAYNES      | HENRY             | DECEASED |
|     62966 |          1 | KRAUS       | JOSEPHINE         | MINOR    |
|     62966 |          2 | KENNEDY     | GEORGE            | DECEASED |
|     62967 |          1 | CAREY       | JAMES             | ESTATE   |
|     62967 |          2 | DE LA GARZA | REFUGIO           | DECEASED |
|     62968 |          1 | LEWIS       | FLORENCE          | ESTATE   |
|     62968 |          2 | LOCKWOOD    | ALBERT A          | DECEASED |
|     62969 |          1 | GLAESER     | EMMA              | MINOR    |
|     62969 |          2 | GLAESER     | HERMAN JR         | MINOR    |
|     62969 |          3 | GLAESER     | HERMAN            | MINOR    |
|     62969 |          4 | RODRIGUEZ   | HILARIO           | DECEASED |
|     62969 |          5 | RODRIGUEZ   | MARIE             | DECEASED |
|     62970 |          1 | STORY       | BETTIE            | ESTATE   |
|     62970 |          2 | EIGENDORFF  | FRANZ             | DECEASED |
|     62971 |          1 | HOWELL      | MAMIE             | MINOR    |
|     62971 |          2 | HOWELL      | ETHEL             | MINOR    |
+-----------+------------+-------------+-------------------+----------+

This type of data extraction is something I am not all too familiar with. I am thinking I need to use a complex combination of SUBSTRING and CHARINDEX, but given that the number of entries that the source column can contain varies, I am not sure how best to approach this. Any guidance on where I should begin would be incredibly helpful.

3
  • Can you process the data outside of the database? That's most likely going to be easier to write and maintain in the long run
    – Erik
    Nov 24 '15 at 2:37
  • @Erik The raw source data was provided to us in a tab delimitted text file and this is one of the columns I have to through and adapt it into our schema. I am not familiar with any other methods (I only know T-SQL). Upon research, I'm thinking I might go about parsing it as XML that I can insert into a temporary table for further manipulation. I have attempted using a split function but I have not had much success. Nov 24 '15 at 2:45
  • 2
    Don't cross post stackoverflow.com/questions/33882999/…
    – paparazzo
    Nov 24 '15 at 10:04
3

As long as the legacy data is as described and does not have any odd variations, the following should work as it produces the desired output.

Notes:

  • The following two code sections should be run together in SSMS as they are using a table variable and hence need to be in the same query batch. I just separated it into two pieces to make it easier to focus on just the main query.
  • The splitter I used, String_Split, is SQLCLR-based and is available in the SQL# library (which I am the author of, but String_Split, and others, are available in the Free version). However, any splitter that returns the row/item # should work just the same (just don't use one that is based on a WHILE loop).

Test setup:

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');

Main query:

;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;
0
1

I use a streaming table-valued function for this sort of thing:

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 will return an Excel-style result set of columns A:CZ. We then select only columns A, C, and Q and add proper typing and aliasing using standard T-SQL. If your data contains a header then you can skip it using Row_Number(). The example also demos how you can easily combine the STVF with Sql Server's FileTables feature.

In your case you'd want to set @delimitChars = ';' and @newLineChars = ','.

C# CLR Code:

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];
    }
}

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.