sql - 使用多个定界符提取数据？

Question

我有一个由分号和逗号分隔的遗留数据源列。第一个分号表示姓氏，第二个分号表示名字和中间名(或首字母)，最后一个分号表示个人的类型。逗号表示新名称已经开始。这是此数据的示例。

+-------+---------------------------------------------------------------------------------------------------------------------+
|  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                                                                               |
+-------+---------------------------------------------------------------------------------------------------------------------+

我正在尝试以如下方式提取数据:

+-----------+------------+-------------+-------------------+----------+
|      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    |
+-----------+------------+-------------+-------------------+----------+

我不太熟悉这种类型的数据提取。我想我需要使用 SUBSTRING 和 CHARINDEX 的复杂组合，但考虑到源列可以包含的条目数各不相同，我不确定如何最好接近这个。任何关于我应该从哪里开始的指导都会非常有帮助。

Answer 1

使用拆分字符串概念和parsename 来做到这一点

SELECT id,
       Row_number()
         OVER (
           partition BY id
           ORDER BY (SELECT NULL ))AS sequence,
       Parsename(Replace(col3, ';', '.'), 3) as LAST,
       Parsename(Replace(col3, ';', '.'), 2) as FIRSTMIDDLE,
       Parsename(Replace(col3, ';', '.'), 1) as TYPE
FROM   (SELECT id,
               Split.a.value('.', 'VARCHAR(100)') col3
        FROM   (SELECT id,
                       Cast ('<M>' + Replace(item_id, ',', '</M><M>')
                             + '</M>' AS XML) AS Data
                FROM   #yourtable) AS A
               CROSS APPLY Data.nodes ('/M') AS Split(a))a