Get number of lines for specific varchar in MS SQL

Question

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.

Basic example:

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');

Now I want to get the total lines of text for each record, i.e., something like this:

--------------------
| ID | LinesOfText |
--------------------
|  1 |           1 |
--------------------
|  2 |           3 |
--------------------
|  3 |           2 |
--------------------

Unfortunately, there doesn't seem to be a built-in functions for something like this. My idea was to count Chr(10)+Chr(13) occurrences and add 1 at the end. But CHARINDEX only finds the first occurrence in a string.

Any idea how to solve this?

Extra information that might be useful: To give a deeper insight to my data, the "Text" is coming from a XML string that contains line breaks, e.g.

...
<a>This is
   another test
   with two line breaks</a>
... 

I use CROSS APPLY XML.nodes(... and XPath to find all <a> nodes in the XML. Could this be solved directly with T-SQL XML functions?

Answer 1

Use Replace to eliminate the line breaks through replacing with nothing (''). Then you can substract the lengh of the modified text from the original.