比较和替换 SQL Server 中字符串中的字符

我有一个字符串说“Hel@1*oO”

Input string -- Hel@1*oO

我想创建一个函数来解析字符串 'Hel@1*oO' 并将字母数字以外的所有字符替换为 #。

基本上我想使用正则表达式作为 [^A-Za-z0-9]。这样除了这些字符之外的所有字符都将被替换为 #

The Output will be -- Hel#1#oO

Oracle 中的 REGEXP_REPLACE() 具有相同的功能，但我需要在 SQL Server 中获得此功能。

可以使用哪组函数来实现此目的。

谢谢您的帮助！

您可能已经发现，T-SQL 不支持正则表达式，因此不支持正则表达式替换。你can如果需要的话，可以使用 CLR 函数实现正则表达式支持，但是，我不会在这里介绍这一点，因为如果您想沿着这条路走下去，已经有大量的资源可供使用。

但是，假设您处于fully支持的 SQL Server 版本，您可以使用 Tally 将字符串分解为单个字符，然后使用以下命令重新聚合字符串STRING_AGG（如果您没有完全支持的版本，则需要使用“旧”版本FOR XML PATH方法）。

这会给你这样的东西：

DECLARE @String nvarchar(4000) = N'Hel@1*oO',
        @Pattern nvarchar(100) = N'[^A-Za-z0-9]',
        @ReplacementCharacter nvarchar(1) = '#';

WITH N AS(
    SELECT N
    FROM (VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL))N(N)),
Tally AS(
    SELECT TOP(LEN(@String))
           ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS I
    FROM N N1, N N2, N N3, N N4)
SELECT STRING_AGG(CASE WHEN V.C LIKE @Pattern THEN @ReplacementCharacter ELSE V.C END,'') WITHIN GROUP (ORDER BY T.I)
FROM Tally T
     CROSS APPLY (VALUES(SUBSTRING(@String,T.I,1)))V(C);

如果您愿意，可以将其转换为内联表值函数，然后将其用于列（或值）：

CREATE OR ALTER FUNCTION dbo.PatternCharacterReplace (@String nvarchar(4000), @Pattern nvarchar(100), @ReplacementCharacter nvarchar(1)) 
RETURNS table
AS RETURN
    WITH N AS(
        SELECT N
        FROM (VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL))N(N)),
    Tally AS(
        SELECT TOP(LEN(@String))
               ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS I
        FROM N N1, N N2, N N3, N N4)  --4096 rows; For a varchar(8000) or MAX you would need more rows for such lengths
    SELECT STRING_AGG(CASE WHEN V.C LIKE @Pattern THEN @ReplacementCharacter ELSE V.C END,'') WITHIN GROUP (ORDER BY T.I) AS ReplacedString
    FROM Tally T
         CROSS APPLY (VALUES(SUBSTRING(@String,T.I,1)))V(C);
GO

SELECT *
FROM (VALUES(N'Hel@1*oO'),('H0w 4re y0u? :)'))V(S)
     CROSS APPLY dbo.PatternCharacterReplace(V.S,N'[^A-Za-z0-9]',N'#') PCR;

请注意，对于该功能，您可能需要为其创建多个版本nvarchar and varchar（并且可能明确地用于MAX长度也一样）

再次，如前所述，如果您需要真正的正则表达式替换功能，则需要查看 CLR 或执行操作outsideSQL Server 的。