title: Sql Server - Remove End String Character "\0" From Data
body: <p>I have a column in the database (SQL Server 2005) that has data with a "\0" at the end.  When querying in SQL Server, this character is not visible and does not "seem" to exist.  When I look in my C# code, the character is there.  This character is causing an error on our website, and we need it removed from all the affected rows.</p>
  
  <p>Is there a sql query I can write to easily remove this character from all the records that are affected?  I can get all the affected records, but I don't have a way to update the record to a new value (without the "\0").</p>
  
  <p>UPDATE:
  This seems to work:</p>
  
  <pre><code>Select * from TABLE
  where UNICODE(SUBSTRING(naughtyField, LEN(naughtyField), 1)) = 0
  </code></pre>
  
  <p>So:</p>
  
  <pre><code>Update TABLE
  SET naughtyField = SUBSTRING(naughtyField, 1, LEN(naughtyField) - 1)
  where UNICODE(SUBSTRING(naughtyField, LEN(naughtyField), 1)) = 0
  </code></pre>