SQL Server REPLACE Characters in Different Tables -

i trying figure out how replace characters in 1 table using characters columns in table in sql server. think best way using cursor , run replace statement unsure how syntax like. have many rows in each table able run once , have loop through table1 , replace values should replaced using data in table2. i've posted example below of want code do.

table1 (contains mystring column):

           mystring     favorite color blue    favorite color red    favorite color green 

table2 (contains origchar , replacechar columns):

        origchar    replacechar           blue         red                             green        red                  our 

table1 result after replace:

       mystring      favorite color red     favorite color red     our favorite color red 

here samples of work far. thoughts of how of these methods work intended.

sample one:

              declare @crowid int;               declare @corigchar varchar(50);               declare @creplacechar varchar(50);               declare @cmystring varchar(50);                declare @mapcursor cursor;                set @mapcursor = cursor               select [rowid]                     ,[mystring]               [dbo].[table1];                 open @mapcursor;               fetch next @mapcursor @crowid,                                               @corigchar,                                               @creplacechar,                                               @cmystring;                  while @@fetch_status = 0                 begin                    update [dbo].[table1]                   set mystring = replace(mystring, origchar, replacechar)                   origchar <> replacechar                  end                  close @mapcursor;                 deallocate @mapcursor; 

sample two:

            declare @rowid int = 0              while (1 = 1)              begin                  select @rowid = rowid               [dbo].[table1]               rowid > @rowid                order rowid                if @@rowcount = 0 break;                    update .[dbo].[table1]                   set mystring = replace(mystring, origchar, replacechar)                   origchar <> replacechar                 select top 1000 [mystring]               [dbo].[table1]                end 

there's better way in cursor, cursor approach, makes more sense loop on pairs of original/replacement values rather looping on every string value. you'll need 2 variables, orig/replace, , while declare cursor, it's not referenced @. also, need add fetch inside while loop in order progress through value pairs, (missing results in infinite loop):

set nocount on declare @origchar varchar(max)        ,@replacechar varchar(max) declare xyz cursor     select distinct origchar,replacechar     table2 open xyz  fetch next xyz  @origchar,@replacechar  while @@fetch_status = 0 begin  update table1 set mystring = replace(mystring,@origchar,@replacechar)      fetch next xyz      @origchar,@replacechar   end close xyz  deallocate xyz  go 

demo: sql fiddle