如何在SQL中创建一个REPLACE PATTERN? [英] How can I make a REPLACE PATTERN in SQL?
本文介绍了如何在SQL中创建一个REPLACE PATTERN?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个长的NVARCHAR变量,我需要替换一些这样的模式:
DECLARE @data NVARCHAR(200) ='你好[PAT1] stackoverflow [PAT2] world [PAT3]'
我需要替换所有 [PAT%]
,空格如下:
'Hello stackoverflow world'
如何在SQL Server 2008中使用T-SQL?
我正在搜索其他问题,我只发现这个,但它并没有帮助我,因为我不需要保留字符串的原始部分。
解决方案
您可以使用此功能进行模式替换。您可以使用此 SQL-Fiddle演示进行测试。
CREATE FUNCTION dbo.PatternReplace
(
@InputString VARCHAR(4000),
@Pattern VARCHAR(100),
@ReplaceText VARCHAR(4000)
)
RETURNS VARCHAR(4000)
AS
BEGIN
DECLARE @Result VARCHAR(4000)SET @Result =''
- 匹配中的第一个字符
DECLARE @First INT
- 在
上开始搜索的下一个字符DECLARE @Next INT SET @Next = 1
- 总字符串 - 8001 if @InputString为NULL
DECLARE @Len INT SET @Len = COALESCE(LEN(@InputString),8001)
- 模式结束
DECLARE @EndPattern INT
WHILE(@Next <= @Len)
BEGIN
SET @First = PATINDEX('%'+ @Pattern +'%',SUBSTRING(@InputString, @Next,@Len))
如果COALESCE(@First,0)= 0 --no match - return
BEGIN
SET @Result = @Result +
CASE - 返回NULL,就像REPLACE一样,如果输入为NULL
WHEN @InputString IS NULL
OR @Pattern IS NULL
OR @ReplaceText IS NULL THEN NULL
ELSE SUBSTRING(@InputString,@Next,@Len)
END
BREAK
END
ELSE
BEGIN
- 匹配前连接字符结果
SET @Result = @Result + SUBSTRING(@InputString,@Next,@First - 1)
SET @Next = @Next + @First - 1
SET @EndPattern = 1
- 查找结束模式范围的开始
WHILE PATINDEX(@Pattern,SUBSTRING(@InputString,@Next,@EndPattern))= 0
SET @EndPattern = @EndPattern + 1
- 查找模式范围的结束
WHILE PATINDEX(@Pattern,SUBSTRING(@InputString,@Next,@EndPattern))> 0
AND @Len> =(@Next + @EndPattern - 1)
SET @EndPattern = @EndPattern + 1
- 在模式结尾或@Next + @EndPattern = @Len
SET @Result = @Result + @ReplaceText
SET @Next = @Next + @EndPattern - 1
END
END
RETURN(@Result)
END
资源链接。
I have a long NVARCHAR variable where I need to replace some pattern like this:
DECLARE @data NVARCHAR(200) = 'Hello [PAT1] stackoverflow [PAT2] world [PAT3]'
I need to replace all [PAT%]
with a blank space to look like:
'Hello stackoverflow world'
How can I do this using T-SQL in SQL Server 2008?
I was searching in other questions, and I only found this, but it doesn't help me, because I don't need to preserve de original part of the string.
解决方案
You may use this function for pattern replace. You can test it with this SQL-Fiddle demo to test.
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, @Next, @Len)
END
BREAK
END
ELSE
BEGIN
-- Concatenate characters before the match to the result
SET @Result = @Result + SUBSTRING(@InputString, @Next, @First - 1)
SET @Next = @Next + @First - 1
SET @EndPattern = 1
-- Find start of end pattern range
WHILE PATINDEX(@Pattern, SUBSTRING(@InputString, @Next, @EndPattern)) = 0
SET @EndPattern = @EndPattern + 1
-- Find end of pattern range
WHILE PATINDEX(@Pattern, SUBSTRING(@InputString, @Next, @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
这篇关于如何在SQL中创建一个REPLACE PATTERN?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文