title: Get number of lines for specific varchar in MS SQL
body: <p>In MS SQL Server 2012 I have a database with a table containing a varchar column that holds some text that might even include line breaks. </p>
  
  <p>Basic example:</p>
  
  <pre><code>CREATE TABLE Example
  (
      [ID] INT
    , [Text] VARCHAR(100)
  );
  
  INSERT INTO Example ([ID], [Text])
  VALUES
          (1, 'This is a test'),
          (2, 'This is another
           test with
           two line breaks'),
          (3, 'This is a test
           with one line break');
  </code></pre>
  
  <p>Now I want to get the total lines of text for each record, i.e., something like this:</p>
  
  <pre><code>--------------------
  | ID | LinesOfText |
  --------------------
  |  1 |           1 |
  --------------------
  |  2 |           3 |
  --------------------
  |  3 |           2 |
  --------------------
  </code></pre>
  
  <p>Unfortunately, there doesn't seem to be a built-in functions for something like this. My idea was to count <code>Chr(10)+Chr(13)</code> occurrences and add 1 at the end. But <code>CHARINDEX</code> only finds the first occurrence in a string. </p>
  
  <p>Any idea how to solve this? </p>
  
  <p><strong>Extra information that might be useful</strong>: To give a deeper insight to my data, the "Text" is coming from a XML string that contains line breaks, e.g.</p>
  
  <pre><code>...
  &lt;a&gt;This is
     another test
     with two line breaks&lt;/a&gt;
  ... 
  </code></pre>
  
  <p>I use <code>CROSS APPLY XML.nodes(...</code> and XPath to find all <code>&lt;a&gt;</code> nodes in the XML. Could this be solved directly with T-SQL XML functions?</p>