Replace Mult Instances of a Pattern in a String

,

This function takes a pattern to be searched for, a string that the pattern will be removed from and a flag to determine whether all instances of the pattern should be removed.

This is sort of an expanded version of a function I wrote to remove multiple spaces from a string.

IF EXISTS (SELECT * 
	   FROM   sysobjects 
	   WHERE  name = N'udf_RemoveMultChars')
	DROP FUNCTION udf_RemoveMultChars
GO

CREATE FUNCTION udf_RemoveMultChars
    -- This is the pattern to search on.  i.e. ' '
   (@Pattern varchar(10), 
    -- This is the string to have multiples removed from
    @StringWithMultChars varchar(255), 
    -- Set = 0 to remove all instances of the pattern 
    @RemoveAll bit) RETURNS varchar(255)
AS
BEGIN
declare @ReplacementString varchar(10),
	@SearchPattern varchar(20)

If @RemoveAll = 0
   begin
   -- remove all instances of the pattern but one.
      set @ReplacementString = @Pattern
      set @SearchPattern = @Pattern+@Pattern
   end
else
   begin
        --  To remove all instances of a pattern 
        --  set @ReplacementString = to an empty string
	set @ReplacementString = ''
	set @SearchPattern = @Pattern
   end

while CHARINDEX(@SearchPattern, @StringWithMultChars) <> 0
   begin
      set @StringWithMultChars = 
              replace(@StringWithMultChars, 
                      @SearchPattern, 
                      @ReplacementString)
   end

RETURN @StringWithMultChars
END
GO

-- =============================================
-- This example uses a pattern of a single space in the 
-- @Pattern variable.
--
-- If, as in this case, you put a 0 in the @RemoveAll
-- variable it recursively searches for two spaces and 
-- replaces them with one.  In the end there will be only
-- one space in each segment of the string where there was
-- one or more spaces.
--
-- If you put a 1 in the @RemoveAll variable it recursively
-- replaces all instances of @Pattern with an empty string.
-- =============================================
SELECT dbo.udf_RemoveMultChars 
	(' ', 'asdf asdf  asdf   asdf', 0)
 
GO

Rate

Share

Share

Rate