用SQL Server替换字符的小函数

目录

介绍

使用输入数据

创建CTE

选择所需的结果

函数的实现

---

• 下载Small_function_to_replace_characters_with_SQL_Server.zip - 695 B

介绍

此提示演示了一个示例，该示例如何利用公共表表达式 （CTE）生成小型SQL Server函数，以替换输入字符串中不需要的字符。虽然此函数可以在SELECT语句中使用，但它也可以在UPDATE语句中用于清理数据，甚至用于WHERE子句中。当用作WHERE子句的一部分时，请记住，性能可能会急剧下降，就像用作条件时的任何功能一样。

相同的功能可以通过许多不同的方式实现，例如使用正则表达式、T-SQL循环等，因此这只是实现此目的的一种方法。

使用输入数据

因此，目标是创建上述函数。让我们把任务分解成几块。我们需要做的第一件事是使用输入字符串和要替换的字符串作为SELECT语句中的数据。这很简单，因为变量可以用作查询中没有任何表的列。请看以下示例

BEGIN
   DECLARE @stringToReplace nvarchar(MAX) = 'this% contains ! illegal/ chars',
           @charsToReplace nvarchar(100)  = '!"#¤%&/()=?',
           @replacement nvarchar(1)       = '';

         SELECT @stringToReplace, @charsToReplace
END;

这将在一行上很好地返回我们的数据

(No column name)                  (No column name)
this% contains ! illegal/ chars   !"#¤%&/()=?

为简单起见，我在本提示后面的查询中使用了这两个变量。

创建CTE

这部分是本提示的基本内容。让我们看一下整个CTE声明

BEGIN
   DECLARE @stringToReplace nvarchar(MAX) = 'this% contains ! illegal/ chars',
           @charsToReplace nvarchar(100)  = '!"#¤%&/()=?',
           @replacement nvarchar(1)       = '';

   WITH
      CharsToReplace (Chars) AS (
         SELECT @charsToReplace
      ),
      InputData (InputString) AS (
         SELECT @stringToReplace
      ),
      ReplaceLoop (Position, SingleChar, OutputString) AS (
         SELECT 1                                AS Position,
                SUBSTRING(ctr.Chars, 1, 1)       AS SingleChar,
                REPLACE(id.InputString,
                     SUBSTRING(ctr.Chars, 1, 1),
                     @replacement)               AS OutputString
         FROM        CharsToReplace ctr
         CROSS APPLY InputData      id
         UNION ALL
         SELECT rl.Position + 1                                AS Position,
                SUBSTRING(ctr.chars, rl.position + 1, 1)       AS SingleChar,
                REPLACE(rl.OutputString,
                     SUBSTRING(ctr.chars, rl.Position + 1, 1),
                     @replacement)                             AS OutputString
         FROM        CharsToReplace ctr
      CROSS APPLY ReplaceLoop    rl
         WHERE LEN(ctr.Chars) > rl.Position
      )
   SELECT *
   FROM ReplaceLoop rl
END;

我认为您已经熟悉常见的表表达式，尤其是递归CTE。如果没有，请查看 WITH common_table_expression（Transact-SQL）。

因此，前两个CTE表只是为了为我们的输入定义单独的表。替换循环是一个递归查询，循环遍历所有需要替换的字符。anchoring语句提取要替换的第一个字符，并用所需的字符进行替换。然后，替换的结果将被后续迭代使用，因为每次迭代都会提取下一个要替换的字符，并为下一次迭代生成自己的输出。

因此，如果执行此语句，则输出将为

Position   SingleChar   OutputString
1          !            this% contains  illegal/ chars
2          "            this% contains  illegal/ chars
3          #            this% contains  illegal/ chars
4          ¤            this% contains  illegal/ chars
5          %            this contains  illegal/ chars
6          &            this contains  illegal/ chars
7          /            this contains  illegal chars
8          (            this contains  illegal chars
9          )            this contains  illegal chars
10         =            this contains  illegal chars
11         ?            this contains  illegal chars

因此，所有艰苦的工作都是由查询完成的。

选择所需的结果

我们绝对不希望查询返回的所有数据，只有当所有替换完成时，最终结果才有意义。为此，可以使用位置列。如果我们简单地根据位置对结果数据进行降序排序并返回第一行（最高位置），我们会得到最后一个结果。

请考虑以下几点

BEGIN
   DECLARE @stringToReplace nvarchar(MAX) = 'this% contains ! illegal/ chars',
           @charsToReplace nvarchar(100)  = '!"#¤%&/()=?',
           @replacement nvarchar(1)       = '';

   WITH
      CharsToReplace (Chars) AS (
         SELECT @charsToReplace
      ),
      InputData (InputString) AS (
         SELECT @stringToReplace
      ),
      ReplaceLoop (Position, SingleChar, OutputString) AS (
         SELECT 1                                AS Position,
                SUBSTRING(ctr.Chars, 1, 1)       AS SingleChar,
                REPLACE(id.InputString,
                     SUBSTRING(ctr.Chars, 1, 1),
                     @replacement)               AS OutputString
         FROM        CharsToReplace ctr
         CROSS APPLY InputData      id
         UNION ALL
         SELECT rl.Position + 1                                AS Position,
                SUBSTRING(ctr.chars, rl.position + 1, 1)       AS SingleChar,
                REPLACE(rl.OutputString,
                     SUBSTRING(ctr.chars, rl.Position + 1, 1),
                     @replacement)                             AS OutputString
         FROM        CharsToReplace ctr
      CROSS APPLY ReplaceLoop    rl
         WHERE LEN(ctr.Chars) > rl.Position
      )
   SELECT *
   FROM ReplaceLoop rl
   ORDER BY rl.position DESC
   OFFSET 0 ROWS
   FETCH FIRST 1 ROWS ONLY;
END;

有了ORDER BY子句，结果是

Position   SingleChar   OutputString
11         ?            this contains  illegal chars

现在，单行上的输出正是我们想要的。

函数的实现

最后一件事是将其包装到一个函数中。基本上唯一要做的就是

• 使用参数定义函数声明
• 查询所需数据并返回

整个函数将如下所示

CREATE FUNCTION ReplaceChars(@stringToReplace nvarchar(MAX),
                             @charsToReplace nvarchar(100),
                             @replacement nvarchar(1))
RETURNS nvarchar(MAX) AS
BEGIN
   DECLARE @returnData  nvarchar(MAX);

   WITH
      CharsToReplace (Chars) AS (
         SELECT @charsToReplace
      ),
      InputData (InputString) AS (
         SELECT @stringToReplace
      ),
      ReplaceLoop (Position, SingleChar, OutputString) AS (
         SELECT 1                                AS Position,
                SUBSTRING(ctr.Chars, 1, 1)       AS SingleChar,
                REPLACE(id.InputString,
                     SUBSTRING(ctr.Chars, 1, 1),
                     @replacement)               AS OutputString
         FROM        CharsToReplace ctr
         CROSS APPLY InputData      id
         UNION ALL
         SELECT rl.Position + 1                                AS Position,
                SUBSTRING(ctr.chars, rl.position + 1, 1)       AS SingleChar,
                REPLACE(rl.OutputString,
                     SUBSTRING(ctr.chars, rl.Position + 1, 1),
                     @replacement)                             AS OutputString
         FROM        CharsToReplace ctr
         CROSS APPLY ReplaceLoop    rl
         WHERE LEN(ctr.Chars) > rl.Position
      )
   SELECT @returnData = rl.OutputString
   FROM ReplaceLoop rl
   ORDER BY rl.position DESC
   OFFSET 0 ROWS
   FETCH FIRST 1 ROWS ONLY;

   RETURN (@returnData);
END;

让我们试一试。如果我们使用与以前相同的输入，则查询会喜欢

SELECT dbo.ReplaceChars('this% contains ! illegal/ chars', '!"#¤%&/()=?', '')

结果将是

(No column name)
this contains  illegal chars

如开头所说，此功能可以在很多地方使用。例如，如果要在清理数据的UPDATE语句中使用它，则查询可能类似于

UPDATE MyTable
SET MyColumn = dbo.ReplaceChars(MyColumn, '!"#¤%&/()=?', '');

https://www.codeproject.com/Tips/5382272/Small-function-to-replace-characters-with-SQL-Serv