SQL - Replace non numeric characters in string -

i'm writing function takes string , has replace non-numeric character 2 numeric characters taken table. code use explore string , find non-numeric characters:

set @string = '1a2b3c4d' set @wcount= 0 set @index = 1 set @len= len(@string)  while @index<= @len  begin     set @char = substring(@string, @index, 1)     if @char '%[a-z]%'       print 'char ' + convert(varchar(10), @index)     else       print @char     set @index= @index+ 1  end 

the output following

1 char 2 2 char 4 3 char 6 4 char 8 

now, when find non-numeric character have replace 2 numeric chars taken table select. e.g.

select @temp = replacement conversion_tab expr = @char 

in conclusion, if have following string '1a2a3a4a' , replacement 'a' '88' resulting string should '188288388488'

thanks in advance help.

bye!

try this

declare @string varchar(100) declare @outstring varchar(100) declare @wcount int declare @temp int declare @index int declare @len int declare @char char  set @string = '1a2a3a4a' set @wcount= 0 set @index = 1 set @len= len(@string)  set @outstring = ''  while @index<= @len  begin     set @char = substring(@string, @index, 1)     if @char '%[a-z]%'     begin       select @temp = replacement #conversion_tab expr = @char       set @outstring = @outstring + convert(varchar(10),@temp)      end     else     begin       set @outstring = @outstring + @char      end     set @index= @index+ 1  end  select @outstring 

sql fiddle demo