Solution to TSQL Beginners Challenge 23

go

if exists (select * from INFORMATION_SCHEMA.ROUTINES where ROUTINE_NAME = 'translate' and ROUTINE_TYPE = 'FUNCTION')
drop function dbo.translate

go

create function dbo.translate
(
@Data varchar(20)
,@DataToReplace VARCHAR(100)
,@ReplacedWithData VARCHAR(100)
)
returns varchar(20)
as
begin
;with test (position, letter, replaceletter)
as
(
	select 1,SUBSTRING(@DataToReplace, 1, 1), substring(@ReplacedWithData, 1, 1)
	union all
	select position + 1, SUBSTRING (@DataToReplace, position + 1, 1), substring(@ReplacedWithData, position + 1, 1)
	from test
	where position < LEN(@DataToReplace)
	)
	
	select @Data = replace(@Data, letter, replaceletter) from test


return @Data
end
	
GO

Tags: