sql-server – 如何在SQL中创建REPLACE PATTERN?
发布时间:2021-03-06 03:05:21 所属栏目:MsSql教程 来源:网络整理
导读:我有一个很长的NVARCHAR变量,我需要替换这样的模式: DECLARE @data NVARCHAR(200) = 'Hello [PAT1] stackoverflow [PAT2] world [PAT3]' 我需要用空格替换所有[PAT%],如下所示: 'Hello stackoverflow world' 如何在SQL Server 2008中使用T-SQL执行此操作?
我有一个很长的NVARCHAR变量,我需要替换这样的模式: DECLARE @data NVARCHAR(200) = 'Hello [PAT1] stackoverflow [PAT2] world [PAT3]' 我需要用空格替换所有[PAT%],如下所示: 'Hello stackoverflow world' 如何在SQL Server 2008中使用T-SQL执行此操作? 我正在寻找其他问题,我只找到了this,但它对我没有帮助,因为我不需要保留字符串的原始部分. 解决方法您可以使用此功能进行图案替换.您可以使用此 SQL-Fiddle demo进行测试以进行测试.CREATE FUNCTION dbo.PatternReplace ( @InputString VARCHAR(4000),@Pattern VARCHAR(100),@ReplaceText VARCHAR(4000) ) RETURNS VARCHAR(4000) AS BEGIN DECLARE @Result VARCHAR(4000) SET @Result = '' -- First character in a match DECLARE @First INT -- Next character to start search on DECLARE @Next INT SET @Next = 1 -- Length of the total string -- 8001 if @InputString is NULL DECLARE @Len INT SET @Len = COALESCE(LEN(@InputString),8001) -- End of a pattern DECLARE @EndPattern INT WHILE (@Next <= @Len) BEGIN SET @First = PATINDEX('%' + @Pattern + '%',SUBSTRING(@InputString,@Next,@Len)) IF COALESCE(@First,0) = 0 --no match - return BEGIN SET @Result = @Result + CASE --return NULL,just like REPLACE,if inputs are NULL WHEN @InputString IS NULL OR @Pattern IS NULL OR @ReplaceText IS NULL THEN NULL ELSE SUBSTRING(@InputString,@Len) END BREAK END ELSE BEGIN -- Concatenate characters before the match to the result SET @Result = @Result + SUBSTRING(@InputString,@First - 1) SET @Next = @Next + @First - 1 SET @EndPattern = 1 -- Find start of end pattern range WHILE PATINDEX(@Pattern,@EndPattern)) = 0 SET @EndPattern = @EndPattern + 1 -- Find end of pattern range WHILE PATINDEX(@Pattern,@EndPattern)) > 0 AND @Len >= (@Next + @EndPattern - 1) SET @EndPattern = @EndPattern + 1 --Either at the end of the pattern or @Next + @EndPattern = @Len SET @Result = @Result + @ReplaceText SET @Next = @Next + @EndPattern - 1 END END RETURN(@Result) END Resource link. (编辑:上饶站长网) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |