用于在 SQL Server 中替换字符的小函数
一个使用 CTE 从字符串中替换不需要的字符的小函数
引言
本技巧展示了如何构建一个小的 SQL Server 函数，该函数利用一个公共表表达式 (CTE) 从输入字符串中替换不需要的字符。虽然这个函数可以在 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 表只是为了定义我们输入的单独表。替换循环是一个递归查询，循环遍历所有需要替换的字符。锚定语句提取第一个要替换的字符，并使用所需的字符进行替换。替换的结果将由后续迭代使用，因为每次迭代都会提取下一个要替换的字符，并为其生成自己的输出以供下一次迭代使用。
所以如果执行此语句，输出将是
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
所以所有的辛苦工作都是由查询完成的。
选择所需的结果
我们绝对不想获取查询返回的所有数据，只有在所有替换完成后，最终结果才有趣。为了实现这一点，可以使用 position 列。如果我们简单地根据位置对结果数据进行降序排序并返回第一行（最高位置），我们就会得到最终结果。
考虑以下内容
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, '!"#¤%&/()=?', '');
历史
- 2024 年 5 月 13 日：创建。