Technical Article

Multiple Replace

,

Like the replace function, but can replace more than 1
value at a time.

e.g. select dbo.multiple_replace('hello', 'e', '1', default) gives 'h1llo'.  Equivalent to: select replace('hello', 'e', '1')

e.g. select dbo.multiple_replace('hello world', 'e;w;ld', '1;2;END', default) gives 'h1llo 2orEND'

e.g. select dbo.multiple_replace('hello world', 'e$w$ld', '1$2$END', '$') gives 'h1llo 2orEND'

CREATE function multiple_replace (@s varchar(1000), @from_list varchar(100), @to_list varchar(100), @separator char(1) = ';') returns varchar(1000) as
begin

  declare @from_len int, @from_CurPos int, @from_PrevPos int
  declare @to_len int,   @to_CurPos int,   @to_PrevPos int
  declare @done bit
  set @from_len = LEN(@from_list) + 1
  set @to_len = LEN(@to_list) + 1
  set @from_CurPos = 1
  set @to_CurPos = 1
  set @from_PrevPos = @from_CurPos
  set @to_PrevPos = @to_CurPos

  WHILE @from_CurPos < @from_len + 1
  BEGIN
    set @done = 0
    IF SUBSTRING(@from_list + @separator, @from_CurPos, 1) = @separator
    BEGIN
      WHILE (not @done = 1) and (@to_CurPos < @to_len + 1)
      BEGIN
        IF SUBSTRING(@to_list + @separator, @to_CurPos, 1) = @separator
        BEGIN
          set @s = replace(@s, SUBSTRING(@from_list, @from_PrevPos, @from_CurPos - @from_PrevPos), SUBSTRING(@to_list, @to_PrevPos, @to_CurPos - @to_PrevPos))
          SET @to_PrevPos = @to_CurPos + 1
          set @done = 1
        END
        SET @to_CurPos = @to_CurPos + 1
      END
      SET @from_PrevPos = @from_CurPos + 1
    END
    SET @from_CurPos = @from_CurPos + 1
  END

  return @s
end

Rate

5 (1)

You rated this post out of 5. Change rating

Share

Share

Rate

5 (1)

You rated this post out of 5. Change rating