如何在 T-SQL 中标识和编辑匹配模式的所有实例

Question

我需要对某些字段运行一个函数来识别和编辑任何 5 位或更长的数字，确保除最后 4 位数字外的所有数字都替换为 *

例如："包含 12345 和 1234 和 12345678 的某些文本"将变为"包含 *2345 和 1234 和 ****5678 的某些文本">

我使用 PATINDEX 来识别模式的起始字符：

PATINDEX('%[0-9][0-9][0-9][0-9][0-9]%', TEST_TEXT)

我可以递归地调用它来获取所有出现的起始字符，但我正在努力进行实际的编辑。

有没有人对如何做到这一点有任何指示？ 我知道使用 REPLACE 将 * 插入它们需要的位置，这只是识别我实际上应该替换的内容，我正在努力解决。

可以在程序上做到这一点，但我需要它是 T-SQL(如果需要，可以是一个函数)。

任何提示非常感谢！

Answer 1

您可以使用 SQL Server 的内置函数执行此操作。此示例中使用的所有这些都存在于 SQL Server 2008 及更高版本中。

DECLARE @String VARCHAR(500) = 'Example Input: 1234567890, 1234, 12345, 123456, 1234567, 123asd456'
DECLARE @StartPos INT = 1, @EndPos INT = 1;
DECLARE @Input VARCHAR(500) = ISNULL(@String, '') + ' '; --Sets input field and adds a control character at the end to make the loop easier.
DECLARE @OutputString VARCHAR(500) = ''; --Initalize an empty string to avoid string null errors
WHILE (@StartPOS <> 0)
BEGIN
SET @StartPOS = PATINDEX('%[0-9][0-9][0-9][0-9][0-9]%', @Input);
IF @StartPOS <> 0
BEGIN
SET @OutputString += SUBSTRING(@Input, 1, @StartPOS - 1); --Seperate all contents before the first occurance of our filter
SET @Input = SUBSTRING(@Input, @StartPOS, 500); --Cut the entire string to the end. Last value must be greater than the original string length to simply cut it all.
SET @EndPos = (PATINDEX('%[0-9][0-9][0-9][0-9][^0-9]%', @Input)); --First occurance of 4 numbers with a not number behind it.
SET @Input = STUFF(@Input, 1, (@EndPos - 1), REPLICATE('*', (@EndPos - 1))); --@EndPos - 1 gives us the amount of chars we want to replace.
END
END
SET @OutputString += @Input; --Append the last element
SET @OutputString = LEFT(@OutputString, LEN(@OutputString))
SELECT @OutputString;

输出以下内容：

示例输入：******7890、1234、*2345、**3456、***4567、123asd456

整个代码也可以作为一个函数制作，因为它只需要一个输入文本。

Answer 2

具有递归 CTE 的脏解决方案

DECLARE 
@tags nvarchar(max) = N'Some text with 12345 and 1234 and 12345678',
@c nchar(1) = N' ';
;
WITH Process (s, i)
as
(
SELECT @tags, PATINDEX('%[0-9][0-9][0-9][0-9][0-9]%', @tags)
UNION ALL 
SELECT value,  PATINDEX('%[0-9][0-9][0-9][0-9][0-9]%', value)
FROM
(SELECT SUBSTRING(s,0,i)+'*'+SUBSTRING(s,i+4,len(s)) value
FROM Process
WHERE i >0) calc
-- we surround the value and the string with leading/trailing ,
-- so that cloth isn't a false positive for clothing
) 
SELECT * FROM Process
WHERE i=0

我认为更好的解决方案是在MS SQL Server中添加clr函数来管理正则表达式。 sql-clr/RegEx

Answer 3

这是一个使用DelimitedSplit8K_LEAD的选项，可以在这里找到。 https://www.sqlservercentral.com/articles/reaping-the-benefits-of-the-window-functions-in-t-sql-2 这是杰夫·莫登(Jeff Moden)拆分器的扩展，甚至比原始的还要快一点。与大多数其他拆分器相比，此拆分器的最大优势是它返回每个元素的序号位置。需要注意的是，我正在使用一个空格根据您的示例数据进行拆分。如果你在其他字符中间塞满了数字，这将忽略它们。这可能是好是坏，具体取决于您的具体要求。

declare @Something varchar(100) = 'Some text with 12345 and 1234 and 12345678';
with MyCTE as
(
select x.ItemNumber 
, Result = isnull(case when TRY_CONVERT(bigint, x.Item) is not null then isnull(replicate('*', len(convert(varchar(20), TRY_CONVERT(bigint, x.Item))) - 4), '') + right(convert(varchar(20), TRY_CONVERT(bigint, x.Item)), 4) end, x.Item)
from dbo.DelimitedSplit8K_LEAD(@Something, ' ') x
)
select Output = stuff((select ' ' + Result 
from MyCTE 
order by ItemNumber
FOR XML PATH('')), 1, 1, '')

这将产生：一些带有 *2345 和 1234 和 ****5678 的文本