SQL Server to Excel using Microsoft Query varchar not showing

Question

I have read a lot of posts that the simplest answer to the issue of special character is to replace your newline characters before running your query. I tried:

REPLACE(REPLACE(Field1, CHAR(10), ' '), CHAR(13), ' ')

I pasted the value into Notepad++ and there is no longer any special characters at least what I can see, I even did a TOP 1 to try only 1 row yet when I run the same query in the Microsoft Query Editor window in Excel the value comes out blank can any shed some light or direct me to another post answer I might have missed.

Answer 1

Not pretty but does the job.

CREATE Function dbo.ufn_RemoveSpecialChars (@str varchar(256)) returns varchar(256)
   with schemabinding
begin
   if @str is null
      return null
   declare @str2 varchar(256)
   set @str2 = ''
   declare @strlen int
   set @strlen = len(@str)
   declare @p int
   set @p = 1
   while @p <= @strlen begin
      declare @code int
      set @code = ascii(substring(@str, @p, 1))
      if @code between 32 and 127 
         set @str2 = @str2 + char(@code)
      set @p = @p + 1
   end
   if len(@str2) = 0
      return null
   return @str2
end