替换重复出现的单词及其前面的字符 [英] Replace a recurring word and the character before it

问题描述

我正在使用 SQL Server 尝试替换每个重复出现的[BACKSPACE]"；在字符串和单词 [BACKSPACE] 之前的字符中，以模仿退格的作用.

I am using SQL Server trying to replace each recurring "[BACKSPACE]" in a string and the character that came before the word [BACKSPACE] to mimic what a backspace would do.

这是我当前的字符串:"这是一个我想要 d[BACKSPACE] 更正的字符串，看看是否可以通过删除退格符之前的单词和 $[BACKSPACE] 字符来使其 %[BACKSPACE] 更干净."

这是我想说的:"这是一个我想更正的字符串，看看是否可以通过删除退格符之前的单词和字符来使其更清晰."

让我更清楚地说明这一点.在上面的示例字符串中，$ 和 % 符号仅用作需要删除的字符示例，因为它们位于我要替换的 [BACKSPACE] 单词之前.

Let me make this clearer. In the above example string, the $ and % signs were just used as examples of characters that would need to be removed since they are before the [BACKSPACE] word that I want to replace.

这是另一个之前的例子:狗 likq[BACKSPACE]是它的主人

Here is another before example: The dog likq[BACKSPACE]es it's owner

我想编辑它以阅读:狗喜欢它的主人

最后一个例子是:我很惊讶[BACKSPACE][BACKSPACE]nlt[BACKSPACE][BACKSPACE]非常惊讶

我想编辑它以阅读:我经常感到惊讶

推荐答案

如果没有提供 Regex 替换的 CLR 函数，您能够做到这一点的唯一方法就是在 T-SQL 中进行迭代.但是请注意，下面的解决方案不会给你你所要求的结果，而是你所要求的逻辑.您声明要删除之前的字符串和字符，但在您的两个场景中并非如此.对于最后 2 个字符串，分别删除 ' %[BACKSPACE]' 和 ' $[BACKSPACE]' (注意前导空格).

Without a CLR function that provides Regex replacement the only way you'll be able to do this is with iteration in T-SQL. Note, however, that the below solution does not give you the results you ask for, but does the logic you ask. You state that you want to remove the string and the character before, but in 2 of your scenarios that isn't true. For the last 2 strings you remove ' %[BACKSPACE]' and ' $[BACKSPACE]' respectively (notice the leading whitespace).

这个前导空格留在这个解决方案中.我不想解决这个问题，因为真正的解决方案是不要为此使用 T-SQL，而使用支持 Regex 的东西.

This leading whitespace is left in this solution. I am not entertaining fixing that, as the real solution is don't use T-SQL for this, use something that supports Regex.

我还假设这个字符串来自表中的一列，并且该表有多个行(每个行都有一个不同的字符串值).

I also assume this string is coming from a column in a table, and said table has multiple rows (with a distinct value for the string on each).

无论如何，解决方案:

WITH rCTE AS(
    SELECT V.YourColumn,
           STUFF(V.YourColumn,CHARINDEX('[BACKSPACE]',V.YourColumn)-1,LEN('[BACKSPACE]')+1,'') AS ReplacedColumn,
           1 AS Iteration
    FROM (VALUES('"This is a string that I would like to d[BACKSPACE]correct and see if I could make it %[BACKSPACE] cleaner by removing the word and $[BACKSPACE] character before the backspace."'))V(YourColumn)
    UNION ALL
    SELECT r.YourColumn,
           STUFF(r.ReplacedColumn,CHARINDEX('[BACKSPACE]',r.ReplacedColumn)-1,LEN('[BACKSPACE]')+1,''),
           r.Iteration + 1
    FROM rCTE r
    WHERE CHARINDEX('[BACKSPACE]',r.ReplacedColumn) > 0)
SELECT TOP (1) WITH TIES
       r.YourColumn,
       r.ReplacedColumn
FROM rCTE r
ORDER BY ROW_NUMBER() OVER (PARTITION BY r.YourColumn ORDER BY r.Iteration DESC);

dB<>fiddle

这篇关于替换重复出现的单词及其前面的字符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！