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 I 猜想), 从我提供的字符串 eg (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();
            }

短途跋涉就照锡上说的做:

    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

这种办法提出两个假设:

That the word you want to truncate is always separated by a space.
That the collation is case insensitive. You can use the COLLATE clause to work around this.

还有,我认为,像 see 这样的单词有问题。如果某人的名字是 John Search 呢?

Answer 2

尝试此 :

UPDATE tableName
   SET fieldName = RTRIM((CASE
                       WHEN CHARINDEX( ( , NameList) = 0
                       THEN NameList
                       ELSE SUBSTRING(NameList, 1, CHARINDEX( ( , NameList) - 1)
                     END))

<强 > UPATE 1

< a href=" "http://sqlfiddle.com/#!3/a33e5/4" rel="nofollow"\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\可以\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\

Answer 3

s让我们假设您所有的后缀都以 ( 开头。您可以做类似的事情 :

Update SOMETABLE
   SET the_name_field = LEFT(the_name_field, CharIndex( ( , the_name_field)-1 )
 Where [conditions are met]
;

友情链接