c# - 替换列中的多个字符串元素

Question

我正在尝试清理 SQL Server 数据库中的一列文本(如果重要，则在 Azure 中)。

文本包含人名，有很多词缀(dr.、Prof.、M.D.、PhD.、MSC，还有很多我不想要的可疑字符,;,;, (, ) 等

我想从这些行中删除很多东西 - 通常在一个字段中删除多次 - 我认为最好的方法是将它们添加到一个表中并使用它来遍历并替换每个和每个都有一个空格，在最终修剪并用一个空格替换多个空格之前，因为嵌套替换公式看起来很疯狂，即使只放置我需要从列中删除的所有术语的 1/3。

最后 - 我想要的输出是将名字拆分为第一个名字、中间名、姓氏。一切都很好，很干净，没有其他信息。

到目前为止，我一直在 SSIS 中处理数据，并探索了标准的 T-SQL 转换(使用嵌套的 replace()，但它增长太多以至于令人讨厌)。我探索了派生列 - 嵌套替换似乎再次成为这里唯一现实的选择!？我终于一直在尝试使用脚本组件找到解决方案，但作为这方面的业余爱好者，我一直无法理解如何使用它，也没有找到任何示例。

效率也是一个问题，因为我最初要处理大约 800,000 个名称。

对于此事的任何帮助，我将不胜感激，因为我已经搜索了很长时间，但没有发现任何似乎有直接帮助的东西。

Answer 1

兔子洞，将全名存储在一个字段中，然后在 T-SQL 中对其进行解析以及所有免责声明的挑战。

无论上述项目如何，清理数据，尤其是 text/varchar/nvarchar 列都是一项挑战，因为您发现嵌套的 REPLACE 可能会失控且难以管理。

您可以在 t-sql 中执行一些操作，以帮助您完成您想要完成的任务。所有这些都需要对数据进行多次传递。我看到这分为 2 个“类别”的清洁。 1 - 删除定义的词，2 - 特殊字符。

• 将“单词”(在您的案例名称前缀和后缀中)放入要删除的表格中。
• 基本上去掉所有非字母字符。

如果我的名字前缀或后缀是“Dean”或“Miss”或...而名字是“Dean Smith”或“Missy”或...怎么办，是的，它就是兔子洞。撇开这些不谈，这里有一些您可以尝试的示例代码。

--Test table for words I want to strip out.
DECLARE @WordsToRemove TABLE
    (
        [word] NVARCHAR(200)
    );

--Test table for my data I want to clean
DECLARE @TestData TABLE
    (
        [Data] NVARCHAR(500)
      , [CleanData] NVARCHAR(500)
    );

--Flag I am using to keep my while loops going
DECLARE @Continue INT;


--Insert of the words I want to remove.
INSERT INTO @WordsToRemove (
                         [word]
                     )
VALUES ( 'DR' )
     , ( 'D.R.' )
     , ( 'M.D.' )
     , ( 'md' )
     , ( 'Prof.' );

--Insert test names to clean.  I have a while loop here, was loading my test table with 10000+ recrods to see performance.
--You can leave the while loop here commented out just to see how the code works.  I got under 2 minutes for 100000+ records.  Your mileage may vary.
--WHILE (SELECT COUNT(*) FROM   @TestData) < 100000
    --BEGIN
        INSERT INTO @TestData (
                              [Data]
                          )
        VALUES ( N'DR Jimmy Smith' )
             , ( 'D.R. John Jones M.D.' )
             , ( 'Timothy Neal DR md' )
             , ( 'Prof. Bob Smith Dr M.D.' )
             , ( 'Taco;,,; Johns Dr. Prof.' )
             , ( 'Prof. ''#%^Special Charaters;,,; Dr. Prof.' );
    --END;

--Just updating a another column so I can save original state
UPDATE @TestData
SET    [CleanData] = [Data]


--Join to my @WordsToRemove table using PATINDEX, continue doing that until all occurrences have been removed.
--Clean out words we dont want
SET @Continue = 1;
WHILE @Continue = 1
    BEGIN
        SET @Continue = 0;
        UPDATE     [a]
        SET        [a].[CleanData] = REPLACE([a].[CleanData], [b].[word], '')
                 , @Continue = 1
        FROM       @TestData [a]
        INNER JOIN @WordsToRemove [b]
            ON PATINDEX('%' + [b].[word] + '%', [a].[CleanData]) > 0;
    END;

--Remove all non-alpha characters, preserving spaces
--PATINDEX using "%[^a-z ]%" looks of anything not a character and not a space.
SET @Continue = 1;
WHILE @Continue = 1
    BEGIN
        SET @Continue = 0;
        UPDATE [a]
        SET    [a].[CleanData] = STUFF([a].[CleanData], PATINDEX('%[^a-z ]%', [a].[CleanData]), 1, '')
             , @Continue = 1
        FROM   @TestData [a]
        WHERE  PATINDEX('%[^a-z ]%', [a].[CleanData]) > 0;
    END;

SELECT *
FROM   @TestData;

然后从那里，根据您的数据存储的任何格式，提取您需要的每个部分。