从提供的字符串开始截断字段的 SQL 语句？

Question

我在 SQL 中有一个大表；一个字段包含用户名，后面通常是我需要剥离的各种内容以获得他们的“普通”名称(不要问!)例如:

<pre>Mark Johnson
Joe Bloggs (DO NOT USE)
Mick Bronson (refer Jim Bloggs)
Jan Morrison
Jemima Thomson refer Joe harrison
Glen Grabs-Moffat try harry

有大约 20 种后缀类型。我想创建一个 UPDATE 查询(我猜可能是 20 个)，它将从我提供的字符串的开头“修剪”值，例如 "(DO"或 "(ref"以获取 "Joe Bloggs"仅没有后缀。最好是不区分大小写的。

有任何想法吗？

谢谢

编辑:

我使用的代码如下所示:

for (int count = 0; count < ExpenseItems.Count; count++)
            {
                ExpenseItems[count].Requester_Name = ExpenseItems[count].Requester_Name.TruncateFromStartOf("(DO NOT").Trim();
                ExpenseItems[count].Requester_Name = ExpenseItems[count].Requester_Name.TruncateFromStartOf("(DON'T").Trim();
                ExpenseItems[count].Requester_Name = ExpenseItems[count].Requester_Name.TruncateFromStartOf("(DONT ").Trim();
                ExpenseItems[count].Requester_Name = ExpenseItems[count].Requester_Name.TruncateFromStartOf("(DONOT").Trim();
                ExpenseItems[count].Requester_Name = ExpenseItems[count].Requester_Name.TruncateFromStartOf(" DO NOT").Trim();
                ExpenseItems[count].Requester_Name = ExpenseItems[count].Requester_Name.TruncateFromStartOf(" DON'T").Trim();
                ExpenseItems[count].Requester_Name = ExpenseItems[count].Requester_Name.TruncateFromStartOf(" DONT ").Trim();
                ExpenseItems[count].Requester_Name = ExpenseItems[count].Requester_Name.TruncateFromStartOf(" DONOT").Trim();
                ExpenseItems[count].Requester_Name = ExpenseItems[count].Requester_Name.TruncateFromStartOf("(pls").Trim();
                ExpenseItems[count].Requester_Name = ExpenseItems[count].Requester_Name.TruncateFromStartOf("(please").Trim();
                ExpenseItems[count].Requester_Name = ExpenseItems[count].Requester_Name.TruncateFromStartOf(" pls").Trim();
                ExpenseItems[count].Requester_Name = ExpenseItems[count].Requester_Name.TruncateFromStartOf(" please").Trim();
                ExpenseItems[count].Requester_Name = ExpenseItems[count].Requester_Name.TruncateFromStartOf("(refer").Trim();
                ExpenseItems[count].Requester_Name = ExpenseItems[count].Requester_Name.TruncateFromStartOf(" refer").Trim();
                ExpenseItems[count].Requester_Name = ExpenseItems[count].Requester_Name.TruncateFromStartOf(" (Re").Trim();
                ExpenseItems[count].Requester_Name = ExpenseItems[count].Requester_Name.TruncateFromStartOf(" (ref to").Trim();
                ExpenseItems[count].Requester_Name = ExpenseItems[count].Requester_Name.TruncateFromStartOf(" ref to").Trim();
                ExpenseItems[count].Requester_Name = ExpenseItems[count].Requester_Name.TruncateFromStartOf(" (refto").Trim();
                ExpenseItems[count].Requester_Name = ExpenseItems[count].Requester_Name.TruncateFromStartOf(" refto").Trim();
                ExpenseItems[count].Requester_Name = ExpenseItems[count].Requester_Name.TruncateFromStartOf(" use ").Trim();
                ExpenseItems[count].Requester_Name = ExpenseItems[count].Requester_Name.TruncateFromStartOf(" try ").Trim();
                ExpenseItems[count].Requester_Name = ExpenseItems[count].Requester_Name.TruncateFromStartOf("(see ").Trim();
                ExpenseItems[count].Requester_Name = ExpenseItems[count].Requester_Name.TruncateFromStartOf(" see ").Trim();
                ExpenseItems[count].Requester_Name = ExpenseItems[count].Requester_Name.TruncateFromStartOf("director").Trim();
                ExpenseItems[count].Requester_Name = ExpenseItems[count].Requester_Name.TruncateFromStartOf(" never ").Trim();
                ExpenseItems[count].Requester_Name = ExpenseItems[count].Requester_Name.TruncateFromStartOf(" moved").Trim();
                ExpenseItems[count].Requester_Name = ExpenseItems[count].Requester_Name.Replace("DISABLED", "(D)").Trim();
                ExpenseItems[count].Requester_Name = ExpenseItems[count].Requester_Name.Replace("disabled", "(D)").Trim();
                ExpenseItems[count].Requester_Name = ExpenseItems[count].Requester_Name.Replace("Disabled", "(D)").Trim();
                ExpenseItems[count].Requester_Name = ExpenseItems[count].Requester_Name.Replace("DISALBED", "(D)").Trim();
            }

truncate 做它在锡上所说的:

    public static string TruncateFromStartOf(this string input, string splitString, bool caseSensitive = false, int offset = 0)
    {
        //Verify input
        if (string.IsNullOrEmpty(input))
            return string.Empty;

        if (string.IsNullOrEmpty(splitString))
            return input;

        int segmentIndex = -1;
        //the start of the segment in the input string
        if (caseSensitive)
        {
            segmentIndex = input.IndexOf(splitString, StringComparison.Ordinal);
        }
        else
        {
            segmentIndex = input.ToLower().IndexOf(splitString.ToLower(), StringComparison.Ordinal);
        }

        if (segmentIndex == -1)
            return input; //nothing to remove

        //Return the parts around the segment
        return input.Substring(0, segmentIndex + offset);
    }

Answer 1

将排除词放在表格中，而不是将它们存储在代码中:

CREATE TABLE dbo.TruncationWords
(
       Word VARCHAR(32) NOT NULL UNIQUE
);

INSERT dbo.TruncationWords(Word) 
SELECT '(DO NOT'
UNION ALL SELECT '(DON''T'
UNION ALL SELECT '(DONT'
UNION ALL SELECT '(DONOT'
UNION ALL SELECT 'DO NOT'
UNION ALL SELECT 'DON''T'
UNION ALL SELECT 'DONT'
UNION ALL SELECT 'DONOT'
UNION ALL SELECT '(pls'
UNION ALL SELECT '(please'
UNION ALL SELECT 'pls'
UNION ALL SELECT 'please'
UNION ALL SELECT '(refer'
UNION ALL SELECT 'refer'
UNION ALL SELECT '(Re'
UNION ALL SELECT '(ref to'
UNION ALL SELECT 'ref to'
UNION ALL SELECT '(refto'
UNION ALL SELECT 'refto'
UNION ALL SELECT 'use'
UNION ALL SELECT 'try'
UNION ALL SELECT '(see'
UNION ALL SELECT 'see'
UNION ALL SELECT 'director'
UNION ALL SELECT 'never'
UNION ALL SELECT 'moved'
UNION ALL SELECT 'disabled';

现在，您可以轻松地针对任何表/列交叉引用这些词。例如:

DECLARE @t TABLE (Name VARCHAR(255));

INSERT @t SELECT 'Mark Johnson'
UNION ALL SELECT 'Joe Bloggs (DO NOT USE)'
UNION ALL SELECT 'Mick Bronson (refer Jim Bloggs)'
UNION ALL SELECT 'Jan Morrison'
UNION ALL SELECT 'Jemima Thomson refer Joe harrison'
UNION ALL SELECT 'Glen Grabs-Moffat try harry'
UNION ALL SELECT 'Can''t touch this';

;WITH x AS
(
  SELECT 
    t.Name, 
    Trunc = LEFT(t.Name, CHARINDEX(' ' + w.Word, t.Name)),
    rn = ROW_NUMBER() OVER (PARTITION BY t.Name ORDER BY CHARINDEX(' ' + w.Word, t.Name))
   FROM @t AS t
   INNER JOIN dbo.TruncationWords AS w
   ON CHARINDEX(' ' + w.Word, t.Name) > 0
)
UPDATE src
  SET src.Name = x.Trunc
  FROM @t AS src
  INNER JOIN x 
  ON src.Name = x.Name
  WHERE x.rn = 1;

SELECT Name FROM @t;

结果:

Name
--------------------------
Mark Johnson
Joe Bloggs
Mick Bronson
Jan Morrison
Jemima Thomson
Glen Grabs-Moffat
Can't touch this

该解决方案有两个假设:

您要截断的单词始终以空格分隔。

排序规则不区分大小写。您可以使用 COLLATE 条款来解决这个问题。

我也认为像 'see' 这样的话有问题。如果有人有名字 'John Seek'怎么办？ ?