title: Troubles with splitting values into multiple rows using T-SQL
body: <p>I have a Sql Server 2K8 R2 DB with a table that have a column containings multiples values, separated by  (char 13 and char 10).</p>
  
  <p>I'm building a script to import the data in a properly normalized schema.</p>
  
  <p>My source table contains something like this :</p>
  
  <pre><code>ID   |    Value
  ________________
  1    |    line 1
            line 2
  ________________
  2    |    line 3
  ________________
  3    |    line 4
            line 5
            line 6
  ________________
  </code></pre>
  
  <p>and so on.</p>
  
  <p><strong>[edit]</strong> FYI, Id is integer and value is nvarchar(3072) <strong>[/edit]</strong></p>
  
  <p>What I want is to query the table to ouput somethnig like this :</p>
  
  <pre><code>ID   |    Value
  ________________
  1    |    line 1
  ________________
  1    |    line 2
  ________________
  2    |    line 3
  ________________
  3    |    line 4
  ________________
  3    |    line 5
  ________________
  3    |    line 6
  ________________
  </code></pre>
  
  <p>I've read many answer here on SO, and also around the web, and I find that using <code>master..sptvalues</code> should be the solution. Especially, I tried to reprodude the solution of the question <a href="https://stackoverflow.com/questions/4250475/split-one-column-into-multiple-rows">Split one column into multiple rows</a>.
  However, without success (suspecting having two chars causing problems).</p>
  
  <p>By now, I wrote this query :</p>
  
  <pre><code>SELECT
      T.ID,
      T.Value, 
      RIGHT(LEFT(T.Value,spt.Number-1),
      CHARINDEX(char(13)+char(10),REVERSE(LEFT(char(13)+char(10)+T.Value,spt.Number-1)))) as Extracted
  FROM 
      master..spt_values spt,
      ContactsNew T
  WHERE
      Type = 'P' AND 
      spt.Number BETWEEN 1 AND LEN(T.Value)+1
      AND
          (SUBSTRING(T.Value,spt.Number,2) = char(13)+char(10) OR SUBSTRING(T.Value,spt.Number,2) = '')
  </code></pre>
  
  <p>This query, unfortunately is returning :</p>
  
  <pre><code>ID   |    Value    |   Extracted
  ________________________________   
  1    |    line 1   |   &lt;blank&gt;
            line 2   |   
  ________________________________   
  1    |    line 1   |   line 2
            line 2   |   
  ________________________________   
  2    |    line 3   |   &lt;blank&gt;
  ________________________________   
  3    |    line 4   |   &lt;blank&gt;
            line 5   |
            line 6   |
  ________________________________   
  3    |    line 4   |   line 5
            line 5   |   line 6
            line 6   |
  ________________________________   
  3    |    line 4   |   line 6
            line 5   |
            line 6   |
  ________________________________  
  </code></pre>
  
  <p><code>&lt;blank&gt;</code> is an empty string, not null string.</p>
  
  <p>I'd appreciate some help to tune my query.</p>
  
  <p><strong>[Edit2]</strong> My source table contains less than 200 records, and performance is not a requirement, so I'm targeting a simple solution rather than an efficient one <strong>[Edit2]</strong></p>
  
  <p><strong>[Edit3]</strong> The source database is readonly. I can't add stored procedure, function, or clr type. I have to do this in a single query. <strong>[Edit3]</strong></p>
  
  <p><strong>[Edit4]</strong> Something strange... it seems that whitespaces are also considered as separators.</p>
  
  <p>If I run the following query :</p>
  
  <pre><code>SELECT
      T.ID,
      replace(T.Value, '#', ' '), 
      replace(RIGHT(
          LEFT(T.Value,spt.Number-1),
          CHARINDEX( char(13) + char(10),REVERSE(LEFT(char(10) + char(13)+T.Value,spt.Number-0)))
          ), '#', ' ')
  FROM 
      master..spt_values spt,
      (   
          select contactID, 
          replace(Value,' ', '#') Value
          from ContactsNew where Value is not null
      ) T
  WHERE
      Type = 'P' AND 
      spt.Number BETWEEN 1 AND LEN(T.Value)+1
      AND
          (SUBSTRING(T.Value,spt.Number,2) =  char(13) + char(10) OR SUBSTRING(T.Value,spt.Number,1) = '')
  </code></pre>
  
  <p>I got the correct number of returns (however, still having wrong values), while running this query :</p>
  
  <pre><code>SELECT
      T.ID,
      T.Value, 
      RIGHT(
          LEFT(T.Value,spt.Number-1),
          CHARINDEX( char(13) + char(10),REVERSE(LEFT(char(10) + char(13)+T.Value,spt.Number-0)))
          )
  FROM 
      master..spt_values spt,
      (   
          select contactID, 
          Value
          from ContactsNew where Value is not null
      ) T
  WHERE
      Type = 'P' AND 
      spt.Number BETWEEN 1 AND LEN(T.Value)+1
      AND
          (SUBSTRING(T.Value,spt.Number,2) =  char(13) + char(10) OR SUBSTRING(T.Value,spt.Number,1) = '')
  </code></pre>
  
  <p>splits on spaces also</p>