title: Break down a delimited string into a temporary table
body: <p>I am sending a delimited string to a 'Stored Procedure' which eventually will be used with an IN statement to update some records, so what im trying to do is create a 'Table-Valued Function' which will do this conversion and return this table that will be usable within this update statement but im unsure how to do this. </p>
  
  <p>There is another function that breaks down delimited strings but it doesnt like like it returns an entire table, i dont really understand its sytax, its not any sql i have come accross before......</p>
  
  <p>So (ill post the other function below) can i modify or use this function for an arbitrary amount of values in this delimited string or do i need to come up with another method?</p>
  
  <pre><code>CREATE FUNCTION [dbo].[Split]
  (
      @String NVARCHAR(4000),
      @Delimiter NCHAR(1)
  )
  RETURNS TABLE
  AS
  RETURN
  (
      WITH Split(stpos,endpos)
      AS(
          SELECT 0 AS stpos, CHARINDEX(@Delimiter,@String) AS endpos
          UNION ALL
          SELECT endpos+1, CHARINDEX(@Delimiter,@String,endpos+1)
              FROM Split
              WHERE endpos &gt; 0
      )
      SELECT 'Id' = ROW_NUMBER() OVER (ORDER BY (SELECT 1)),
          'Data' = SUBSTRING(@String,stpos,COALESCE(NULLIF(endpos,0),LEN(@String)+1)-stpos)
      FROM Split
  )
  </code></pre>
  
  <p>when using this function ill get each item with syntax like the following</p>
  
  <pre><code>set @ID = (select Data from dbo.Split(@Params, '|') where ID = 1)
  </code></pre>
  
  <p>*****UPDATE WITH RESULTS FROM THE GIVEN ANSWER*******</p>
  
  <pre><code>DECLARE @pVals VARCHAR(MAX)
  
  SET @pvals = '1,2,3,4,5'
  
  DECLARE @tblDelimitedData TABLE (
      [Data] INT NULL
      )
  
  INSERT INTO @tblDelimitedData(data)
      SELECT data
      FROM dbo.Split(@pvals, ',') s
      WHERE ID &gt;= 2
  
  SELECT * FROM @tblDelimitedData
  </code></pre>
  
  <p>Results are 2, 3, 4, 5</p>
  
  <p>Perfect Thanks!!</p>