用特殊字符替换一组连续的数字

有两个因素对性能很重要：

1. 减少字符串操作的数量。

   您可能会发现可以使用 eg 来实现您需要的内容CHARINDEX并找到组的开始和结束，而不是每次都对整个字符串PATINDEX执行很多操作。REPLACE

2. 使用提供正确结果的最便宜的排序规则。

   二进制排序规则是最便宜的。SQL 排序规则（仅在非 Unicode 数据上）要贵一些。Windows 排序规则要贵得多。

例如：

DECLARE @T table
(
    SampleID varchar(200) NOT NULL UNIQUE
);

INSERT @T
    (SampleID)
VALUES
    ('ABC123124_A12312'),
    ('ABC123_A1212'),
    ('ABC123124_B12312'),
    ('AC123124_AD12312'),
    ('A12312_123'),
    ('999ABC888DEF');

SELECT
    T.SampleID,
    Pattern =
    (
        SELECT
            CASE
                WHEN Chars.this NOT LIKE '[0123456789]' THEN Chars.this
                WHEN Chars.prev NOT LIKE '[0123456789]' THEN '*'
                ELSE ''
            END
        FROM dbo.Numbers AS N
        OUTER APPLY
        (
            SELECT 
                SUBSTRING(Bin.string, N.n, 1),
                SUBSTRING(Bin.string, N.n + 1, 1)
        ) AS Chars (prev, this)
        WHERE
            N.n BETWEEN 1 AND LEN(Bin.string)
        ORDER BY N.n
        FOR XML PATH ('')
    )
FROM @T AS T
OUTER APPLY (VALUES('$' + T.SampleID COLLATE Latin1_General_100_BIN2)) AS Bin (string);

db<>小提琴演示

该示例依赖于一个永久的数字表。如果需要，足够的表格varchar(200)是：

-- Create a numbers table 1-200 using Itzik Ben-Gan's row generator
WITH
  L0   AS (SELECT 1 AS c UNION ALL SELECT 1),
  L1   AS (SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),
  L2   AS (SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),
  L3   AS (SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),
  L4   AS (SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),
  L5   AS (SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B),
  Nums AS (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS n FROM L5)
SELECT
    -- Destination column type integer NOT NULL
    ISNULL(CONVERT(integer, N.n), 0) AS n
INTO dbo.Numbers
FROM Nums AS N
WHERE N.n >= 1
AND N.n <= 200
OPTION (MAXDOP 1);

-- Add clustered primary key
ALTER TABLE dbo.Numbers
ADD CONSTRAINT PK_Numbers_n
PRIMARY KEY CLUSTERED (n)
WITH (SORT_IN_TEMPDB = ON, MAXDOP = 1, FILLFACTOR = 100);

如果这不是更快，您可能会发现单独使用二进制排序规则会充分加快现有实现的速度。要实现这一点，请将您的代码的一行更改为：

(REPLACE(SampleID COLLATE Latin1_General_100_BIN2, '0', '*'),

SQL Server 2017 或更高版本的用户可以利用内置TRANSLATE函数，它的性能可能比嵌套REPLACE调用更好。

您还可以使用通用的正则表达式 CLR 函数，或者在 SQLCLR 中为这个特定任务实现一些自定义的东西。参见例如SQL Server：用通配符替换？

使用SQL# 库，一个完整的解决方案是：

SELECT 
    T.SampleID,
    SQL#.RegEx_Replace4k(T.SampleID, '\d+', '*', -1, 1, 'CultureInvariant')
FROM @T AS T;

完整的正则表达式支持对于这个任务来说是多余的，所以如果你能够使用 SQLCLR，为你的需要编写一个特定的函数可能是所有性能最好的解决方案。

以您喜欢的任何方式创建数字表，

create table tblnumber(number int not null)
insert into tblnumber (number)
select ROW_NUMBER()over(order by a.number) from master..spt_values a
, master..spt_values b
CREATE unique clustered index CI_num on tblnumber(number)

或者您2000 ,3000只能在 tblNumber 中编号，因为没有字符串会那么长。缩短一张数字表。

使用 ITVF，

ALTER FUNCTION [dbo].[fn_Mask] (@pString VARCHAR(4000))
    --WARNING!!! DO NOT USE MAX DATA-TYPES HERE!  IT WILL KILL PERFORMANCE!
RETURNS TABLE
    WITH SCHEMABINDING
AS
RETURN
WITH CTE AS (
        SELECT t.number AS N
            ,SUBSTRING(@pString, t.number, 1) col
        FROM dbo.tblNumber T
        WHERE t.number <= DATALENGTH(@pString)
        )
    ,CTE1 AS (
        SELECT c.N
            ,CASE 
                WHEN patindex('%[0-9]%', c.col) = 0
                    THEN c.col
                ELSE oa.col2
                END col1
        FROM CTE c
        OUTER APPLY (
            SELECT TOP 1 '*' AS col2
            FROM CTE c1
            WHERE c.N - c1.N = 1
                AND patindex('%[0-9]%', c1.col) = 0
                AND patindex('%[0-9]%', c.col) = 1
            ORDER BY c1.N
            ) oa
        )

SELECT TOP 1 (
        SELECT '' + col1
        FROM CTE1
        WHERE N > 1
            AND col1 IS NOT NULL
        ORDER BY N
        FOR XML path('')
        ) MaskedString
FROM CTE1 C;

用法 ：

    DECLARE @T table
(
    SampleID varchar(200) NOT NULL UNIQUE
);

INSERT @T
    (SampleID)
VALUES
    ('ABC123124_A12312'),
    ('ABC123_A1212'),
    ('ABC123124_B12312'),
    ('AC123124_AD12312'),
    ('A12312_123'),
    ('A$B.C-D+E'),
    ('A2B.C-D+E'),
    ('999ABC888DEF');

    --Prefix one extra non numeric charector.it do not change output
    select SampleID,MaskedString from @T T
    cross apply(select MaskedString from [dbo].[fn_Mask]('F'+t.SampleID))ca

select MaskedString from [dbo].[fn_Mask]('F'+'999ABC888DEF')