title: SQL Server - Remove all non-printable ASCII characters
body: <p>We recently migrated from SQL Server 2012 to SQL Server 2014 and all our <code>FOR XML</code> code started throwing errors about non-printable ASCII characters.
  I wrote this <em>horrible</em> function to remove non-printable ASCII characters as a quick fix. I want to replace it with something cleaner. Is there a way to do this?</p>
  
  <pre><code>ALTER FUNCTION [dbo].[remove_non_printable_chars]
  (@input_string nvarchar(max))
  RETURNS nvarchar(max)
  BEGIN
      RETURN
      REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
      REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
      REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
      REPLACE(REPLACE(@input_string,
          CHAR(1), ''),CHAR(2), ''),CHAR(3), ''),CHAR(4), ''),CHAR(5), ''),CHAR(6), ''),CHAR(7), ''),CHAR(8), ''),CHAR(9), ''),CHAR(10), ''),
          CHAR(11), ''),CHAR(12), ''),CHAR(13), ''),CHAR(14), ''),CHAR(15), ''),CHAR(16), ''),CHAR(17), ''),CHAR(18), ''),CHAR(19), ''),CHAR(20), ''),
          CHAR(21), ''),CHAR(22), ''),CHAR(23), ''),CHAR(24), ''),CHAR(25), ''),CHAR(26), ''),CHAR(27), ''),CHAR(28), ''),CHAR(29), ''),CHAR(30), ''),
          CHAR(31), ''), NCHAR(0) COLLATE Latin1_General_100_BIN2, '')
  END
  </code></pre>
  
  <p>Here's the <code>FOR XML</code> code that broke. (I did not write this. It was already in the code base).</p>
  
  <pre><code>SELECT @HTMLTableData =
  (
      SELECT  HTMLRows 
      FROM (
          SELECT N'&lt;tr&gt;' + HTMLRow + N'&lt;/tr&gt;' AS HTMLRows 
          FROM @HTMLRowData
      ) mi            
      FOR XML PATH(''), TYPE
  ).value('/', 'NVARCHAR(MAX)')
  </code></pre>