title: Why (and how) to split column using master..spt_values?
body: <p>Subquestioning the answer to question <a href="https://stackoverflow.com/questions/4250475/split-one-column-into-multiple-rows/4250498#4250498">"Split one column into multiple rows"</a> which I re-wrote here as [ 1 ].  </p>
  
  <p>What is the (meaning of) <code>Type = 'P'</code> and why to use undocumented master..spt_values for splitting a column? What is the benefit of it?  </p>
  
  <hr>
  
  <p>[ 1 ]  </p>
  
  <pre><code>CREATE TABLE dbo.Table1 
  (
      Col1        CHAR(1),
      Col2        CHAR(1),
      Col3        CHAR(1),
      Col4        VARCHAR(50)
  )
  GO
  
  INSERT INTO dbo.Table1 VALUES ('A','B','C','1,2,3')
  GO
  INSERT INTO dbo.Table1 VALUES ('D','E','F','6,7,8,9')
  GO
  
  
  SELECT
      T.col1, RIGHT(LEFT(T.col4,Number-1),
      CHARINDEX(',',REVERSE(LEFT(','+T.col4,Number-1))))
  FROM
      master..spt_values,
      table1 T
  WHERE
      Type = 'P' AND Number BETWEEN 1 AND LEN(T.col4)+1 AND
      (SUBSTRING(T.col4,Number,1) = ','
      -- OR SUBSTRING(T.col4,Number,1)  = '') --this does not work correctly anyway
  </code></pre>
  
  <p>Related question:  </p>
  
  <ul>
  <li><a href="https://stackoverflow.com/questions/4273723/what-is-the-purpose-of-system-table-table-master-spt-values-and-what-are-the-m">What is the purpose of system table table master..spt_values and what are the meanings of its values?</a></li>
  </ul>