question

I would probably say that it is the CHARINDEX that is the difference, run the following query on your different servers and see if one returns NULL and the other returns 0, therefore when the charindex is null, and the @position is being set the @position value = NULL and the loop stops. select charindex('test',NULL,1) Books online say this about the CHARINDEX function, so by your description it looks as if your SQL server 2008 database compatibility is set to 65 or lower? (Is that possible?, I don't have a 2008 version so I can't test) > If either expression1 or expression2 > is NULL, CHARINDEX returns NULL when > the database compatibility level is 70 > or higher. If the database > compatibility level is 65 or lower, > CHARINDEX returns NULL only when both > expression1 and expression2 are NULL.

To my mind the issue is where you are starting @Counter and @Position values. If you change your code to DECLARE @Value VARCHAR(MAX) DECLARE @delimiter CHAR(2) DECLARE @ConcatenatedList VARCHAR(MAX) DECLARE @Position INT DECLARE @Counter INT SET @delimiter='||' SET @ConcatenatedList = '001||002||003||004' SET @Position = CHARINDEX(@delimiter, @ConcatenatedList, 0) + 1 SET @Counter = 0 WHILE @Position > 0 AND @Counter < 25 BEGIN PRINT 'Loop Count: ' + CAST(@Counter AS VARCHAR) + '; Position: '+ CAST(@position AS CHAR(4)) + '; List: ''' + @ConcatenatedList + '''' PRINT '---' SET @ConcatenatedList = RIGHT(@ConcatenatedList, LEN(@ConcatenatedList) - @Position) SET @Position = CHARINDEX(@delimiter, @ConcatenatedList, 1) + 1 SET @Counter = @Counter + 1 END You can see that the charindex function is returning 0 (because the @delimiter, @ConcatenatedList are not null but there is no match) and you are adding 1. If you change SET @Counter = 0 WHILE @Position > 0 AND @Counter < 25 to be SET @Counter = 1 WHILE @Position > 1 AND @Counter < 25 and re-run I think you get the correct output

I was looking on this a little found, that the difference is caused by the RIGHT function. If you test this on SQL Server 2005 SELECT RIGHT(CAST('4' AS VARCHAR(max)), 0) result will be NULL if you run SELECT RIGHT(CAST('4' AS VARCHAR(20)), 0) result will be and empty text SQL Server 2008 both commands returns empty text. So on the SQL server 2005 there is different behavior when you use `varchar(max)` as expression and want to return 0 characters, the SQL 2005 return NULL In BOL there is slightly different description of `integer_expression` for SQL 2005 and SQL 2008, but didn't find anything about this behavior. If you take a look on the SQL 2005 compatibility level description in [BOL][1] you will find, that the that there is difference between compatibility levels, that RIGHT('123', 0) for lower compatibility levels return NULL and for compatibility level 90 it returns empty string. But the test shows, that if you pass a varchar(max) it return NULL like in lower compatibility levels. But nothing is stated about this behavior for varchar(max). [RIGHT - BOL 2008][2] [RIGHT - BOL 2005][3] For your example then when the new position is calculated in the SET @Position = CHARINDEX(@delimiter, @ConcatenatedList, 1) + 1 then in the Step 5 on SQL Server 2005 there goes NULL as @ConcatenatedList and therefore result is NULL and the Condition in the loop is not met However on SQL Server 2008 there goes an empty string as @ConcatenatedList and therefore the result is 1 in step 5 and all further steps. [1]: http://msdn.microsoft.com/en-us/library/ms178653%28SQL.90%29.aspx [2]: http://msdn.microsoft.com/en-us/library/ms177532.aspx [3]: http://msdn.microsoft.com/en-us/library/ms177532%28SQL.90%29.aspx