Is there any way to do HTML decode in SQL Server?

Question

I have the following records in one of my table

CD&M Communications 
auburndale oil & propane inc  
C F La Fountaine #7561  
Laramie County Fire District # 2  
AmeriGas Propane LP #2250  

Is there a way to remove the characters like &, #7561, #2250 etc.

"&" should be replaced with "&" as per C# HTMLDECODE function

Answer 1

There is a much easier solution...

SQL Server supports the XML datatype, and it supports decoding XML/HTML encoded entities. If you just cast the string to the XML datatype, you can use the built in decode function.

That would look like this:

select cast('Q & A' as XML).value('.[1]','nvarchar(max)' );

To turn it into a function for easy use:

create function dbo.xmlDecode (@string nvarchar(max))
returns varchar(max)
begin
    return cast(@string as XML).value('.[1]','nvarchar(max)' )
end;

Keep in mind that in OP's example, the string seems to have been encoded 3 times in a row. & got turned into & then into & and then into &. As a result, to get the "original" string back, you have to use the decode function 3 times.