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
+1 @Nico , I needed a function that will remove special charters from a string, so I adjusted your function a little bit to be able to do this:
select dbo.PatReplaceAll('St. Martin`n tr‘an, or – in - the * field007', '[^0-9A-Z ]', '')
--Returns 'St Martinn tran or in the field007'
Here is the function:
CREATE FUNCTION dbo.PatReplaceAll
(
@Source varchar(8000),
@Pattern varchar( 50),
@Replace varchar( 100)
)
RETURNS varchar(8000)
AS
BEGIN
if @Source is null or @Pattern is null or @Replace is null
return null
if PATINDEX('%' + @Pattern + '%', @Source) = 0
return @Source
-- Declare the return variable here
DECLARE @Result varchar(8000) SET @Result = ''
-- The remainder of the @Source to work on
DECLARE @Remainder varchar(8000) SET @Remainder = @Source
DECLARE @Idx INT
WHILE (LEN(@Remainder) > 0)
BEGIN
SET @Idx = PATINDEX('%' + @Pattern + '%', @Remainder)
IF @Idx = 0 --no match - return
BEGIN
SET @Result = @Result + @Remainder
BREAK
END
-- Concatenate characters before the match to the result
SET @Result = @Result + SUBSTRING(@Remainder, 1, @Idx - 1)
-- Adjust the remainder
SET @Remainder = SUBSTRING(@Remainder, @Idx, LEN(@Remainder) + 1 - @Idx)
SET @Idx = 1
-- Find the last char of the pattern (aka its length)
WHILE PATINDEX(@Pattern, SUBSTRING(@Remainder, 1, @Idx)) = 0
SET @Idx = @Idx + 1
--remove the pattern from the remainder
SET @Remainder = SUBSTRING(@Remainder, @Idx + 1, LEN(@Remainder) - @Idx)
--Add the replace string
SET @Result = @Result + @Replace
END
return @Result
END
GO
