title: Removing anything after first newline in SQL Server select statement
body: <p>I have a column 'Name' that contains newlines. We want to remove the newline as well as anything after the first instance of a newline.</p>
  <p>The first step I did was replacing a newline with an empty string and it worked:</p>
  <pre><code>REPLACE(REPLACE(Name, CHAR(13), ''), CHAR(10), '')
  </code></pre>
  <p>Then, I tried replacing any newline with a '----' instead so I can use it with charindex() and only get anything left of it:</p>
  <pre><code>LEFT(REPLACE(REPLACE(Name, CHAR(13), '----'), CHAR(10), '----'), CHARINDEX('----',REPLACE(REPLACE(Name, CHAR(13), '----'), CHAR(10), '----'))-1)
  </code></pre>
  <p>This gives an error &quot;Invalid length parameter passed to the LEFT or SUBSTRING function.&quot; which is to be expected because not all the names have a newline.</p>
  <p>To account for those that dont have a new line I used substring() and case() wherein if it has no newline it will the substring length of the original else it will use charindex('----',...) instead.</p>
  <pre><code>  SUBSTRING(REPLACE(REPLACE(Name, CHAR(13), '----'), CHAR(10), '----'),1,
    CASE WHEN CHARINDEX('----',REPLACE(REPLACE(Name, CHAR(13), '----'), CHAR(10), '----')) = 0 THEN LEN(REPLACE(REPLACE(Name, CHAR(13), '----'), CHAR(10), '----'))
    ELSE CHARINDEX('----',REPLACE(REPLACE(Name, CHAR(13), '----'), CHAR(10), '----'))-1 END)
  </code></pre>
  <p>Is there a more intuitive way of going about this where I don't have to replace the newlines with a string using that string as a reference point and using the newlines as a reference point instead?</p>