I would like to create a function that will replace a string using a reference table

I have a table : reference

If I pass a string into a function,  function needs to replace a string with replace with string column in reference table

For example, if I pass    a string : car $ap; fjld

The function should return car & fjld

How can i create a function like this so that i can call it in Stored procedure/

create function fn_replace (@s nvarchar(100))
returns nvarchar(100)
as
begin

return replace(replace(REPLACE (@s,'&ap;','&'),'&lt;','<'),'&gt;','>')
end

declare @s nvarchar(100)=' car &ap; fjld'

select dbo.fn_replace(@s)

What did you try so far? You would need a loop in your function, as the string can have multiple occurrences of strings to replace, so you can write a loop 

while exists (select 1 from Reference where charindex([String], @cString) > 0)

  and here manipulate with the string using replace function.

-------------

Also you need to be sure that all Strings are unique in the reference table. 

This is not a simple solution.  It is very similar to solutions for splitting strings using T-SQL.  Optimal solutions are complex, so I would recommend you research the link below to see various approaches to traversing strings in T-SQL.  Create a solution that you are comfortable with and can support.  By support, I mean that you should be able to explain it clearly to anyone who asks.  Otherwise, you won't effectively be able to debug any issues that arise from it and neither can anyone else.

What did you try so far? You would need a loop in your function, as the string can have multiple occurrences of strings to replace, so you can write a loop 

while exists (select 1 from Reference where charindex([String], @cString) > 0)

  and here manipulate with the string using replace function.

-------------

Also you need to be sure that all Strings are unique in the reference table. 

Thanks Naomi,

When I read your answer, I understood that the reference table doesn't contain just 3 rows.

so here is the fucntion:

CREATE FUNCTION fn_replace (@s NVARCHAR(100))
RETURNS NVARCHAR(100)
AS
BEGIN
WHILE exists (SELECT 1 FROM reference WHERE charindex(String, @s) > 0)
 SELECT @s=REPLACE (@s,String,ReplaceWith) FROM reference
RETURN @s
END

Did you test that function providing input containing several strings to replace in one string? 

Yes.

will return

' car & fj<;ld &'

What did you try so far? You would need a loop in your function, as the string can have multiple occurrences of strings to replace, so you can write a loop 

while exists (select 1 from Reference where charindex([String], @cString) > 0)

  and here manipulate with the string using replace function.

-------------

Also you need to be sure that all Strings are unique in the reference table. 

Thanks Naomi,

When I read your answer, I understood that the reference table doesn't contain just 3 rows.

so here is the fucntion:

CREATE FUNCTION fn_replace (@s NVARCHAR(100))
RETURNS NVARCHAR(100)
AS
BEGIN
WHILE exists (SELECT 1 FROM reference WHERE charindex(String, @s) > 0)
 SELECT @s=REPLACE (@s,String,ReplaceWith) FROM reference
RETURN @s
END

That is an interesting solution.  You should replace "SELECT 1" with "SELECT *" since you are using an EXISTS operator.  More importantly, T-SQL queries execute all at once.  How do you ensure that @s is being updated optimally?  I understand that the loop will cycle indefinitely until everything is replaced, but @s may be updated with the wrong row numerous times before WHILE condition becomes false.  There doesn't seem to be a way to deterministically iterate through each replacement condition.

If all strings are unique in the reference table, @s is updated optimally because it's replaced each time

SELECT @s=REPLACE (@s,string,replacewith) FROM reference

I was thinking more about doing something like (pseudo-code)

do while true

   select top (1) @String = String, @Replace = Replace

from Replacement where charindex(string, @Input) > 0

   if @@rowcount = 0

       break ; -- exit the loop

   set @input = replace(@input, @String, @Replace)

That is an interesting solution.  You should replace "SELECT 1" with "SELECT *" since you are using an EXISTS operator.  More importantly, T-SQL queries execute all at once.  How do you ensure that @s is being updated optimally?  I understand that the loop will cycle indefinitely until everything is replaced, but @s may be updated with the wrong row numerous times before WHILE condition becomes false.  There doesn't seem to be a way to deterministically iterate through each replacement condition.

If all strings are unique in the reference table, @s is updated optimally because it's replaced each time

SELECT @s=REPLACE (@s,string,replacewith) FROM refer		

USE TestDB
GO

CREATE TABLE reference --drop table reference
(
ID INT,
String NVARCHAR(99),
ReplaceWith NVARCHAR(99)
);

INSERT INTO reference
VALUES(1,'&ap;','&'),(2,'&lt;','<'),(3,'&gt;','>');
GO

CREATE PROC procToRepalce(@s NVARCHAR(2000)) --drop proc procToRepalce
AS
BEGIN

DECLARE @RepStr NVARCHAR(2000);

;WITH Cte AS
(
SELECT String,ReplaceWith,ROW_NUMBER() OVER(ORDER BY LEN(String) DESC) RN FROM reference
)--SELECT 1 rn2, CAST(('REPLACE(@s,'''+String+''','''+ReplaceWith+''')') AS NVARCHAR(MAX)) RepStr FROM Cte WHERE rn=1
,
Cte2 AS
(
SELECT 1 rn2, CAST(('REPLACE(@s,'''+String+''','''+ReplaceWith+''')') AS NVARCHAR(MAX)) RepStr FROM Cte WHERE rn=1
UNION ALL
SELECT rn2+1, CAST(('REPLACE('+RepStr+','''+String+''','''+ReplaceWith+''')') AS NVARCHAR(MAX)) FROM Cte2 c2 JOIN Cte c
					ON c2.rn2+1=c.rn
)
SELECT @RepStr=RepStr FROM cte2 c WHERE NOT EXISTS(SELECT 1 FROM Cte2 WHERE rn2>c.rn2)

SET @RepStr = 'SELECT ' + @RepStr
 PRINT @RepStr
EXEC sp_executesql @RepStr, N'@s NVARCHAR(2000)',@s=@s
END
 ;
GO

DECLARE @s NVARCHAR(2000)=N'&lt;car &ap; wheels &gt;'
EXEC procToRepalce @s
/*
<car & wheels >
*/