title: SQL server : split string function only return part of string
body: <p>I found this SQL split string function on internet, but when I pass a string contains 700+ items in it, separated by commas, it only generate a table variable with 280 rows, can some help me to identify where's the problem?</p>
  
  <p>The code:</p>
  
  <pre><code>ALTER  FUNCTION [dbo].[fn_Split](@text nvarchar(MAX), @delimiter varchar(20) = ' ')
  RETURNS @Strings TABLE
  (    
    position int IDENTITY PRIMARY KEY,
    value nvarchar(MAX)   
  )
  AS
  BEGIN
  
  DECLARE @index int
  SET @index = -1 
  
  WHILE (LEN(@text) &gt; 0) 
  
    BEGIN 
      SET @index = CHARINDEX(@delimiter , @text)  
      IF (@index = 0) AND (LEN(@text) &gt; 0)  
        BEGIN  
          INSERT INTO @Strings VALUES (@text)
            BREAK  
        END 
  
      IF (@index &gt; 1)  
        BEGIN  
          INSERT INTO @Strings VALUES (LEFT(@text, @index - 1))   
          SET @text = RIGHT(@text, (LEN(@text) - @index))  
        END 
      ELSE
        SET @text = RIGHT(@text, (LEN(@text) - @index)) 
      END
    RETURN
  
  END
  </code></pre>
  
  <p>This is the code I used to test it:</p>
  
  <pre><code> SELECT * FROM fn_Split(@string,',');
  </code></pre>
  
  <p>@String is nvarchar(MAX) contains 700+ items, but it only returns a table with 280 rows.</p>