从 SQL Server 中的字符串中删除子字符串的第二次出现 [英] Remove second appearence of a substring from string in SQL Server

问题描述

我需要从主字符串中删除子字符串的第二次出现，如果两个子字符串彼此相邻.例如:

I need to remove the second appearance of a substring from the main string, IF both substrings are next to each other. e.g.:

Jhon\Jhon\Jane\Mary\Bob 需要结束 Jhon\Jane\Mary\Bob

但是Mary\Jane\Mary\Bob 必须保持不变.

谁能想出一种高效的方法来做到这一点?

Can anyone can come out with a performant way to do this?

'\' 是不同名称的分隔符，因此可以作为替换子串的限制.

'\' is the separator of different names, so it can be use as limit of the substring to replace.

EDIT:这是在 SELECT 语句上运行的，所以它应该是一行解决方案，我不能使用变量.此外，如果名称在其他任何地方重复，我必须让它们在那里.如果第一个和第二个名称相同，则仅删除一次.

EDIT: this is to be run on a SELECT statement, so it should be a one line solution, I can't use variables. Also, if the names are repetaed anywhere else, I have to let them there. Only remove one occurrence if both the first and the second names are the same.

推荐答案

我决定进行字符串操作.我认为执行查询需要更长的时间，但是在...ejem...生产环境...ejem...我发现它没有(令我惊讶).它不漂亮，我知道，但很容易维护...

I decided to go for string manipulation. I thought it'd take longer to execute the query, but testing it in... ejem... production environment... ejem... I found out that it did not (much to my surprise). It ain't pretty, I know, but it's easy to mantain...

这是我最终查询的简化版本:

Here is a simplified version of my final query:

SELECT SOQ.PracticeId,
       CASE WHEN LEFT(SOQ.myString, SOQ.SlashPos) = SUBSTRING(SOQ.myString, SOQ.SlashPos + 1, LEN(LEFT(SOQ.myString, SOQ.SlashPos)))
            THEN RIGHT(SOQ.myString, LEN(SOQ.myString) - SOQ.SlashPos)
            ELSE SOQ.myString
       END as myString
  FROM (SELECT OQ.AllFields, OQ.myString, CHARINDEX('\', OQ.myString, 0) as SlashPos
          FROM MyOriginalQuery OQ) SOQ

这篇关于从 SQL Server 中的字符串中删除子字符串的第二次出现的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！