Invalid length parameter passed to the LEFT or SUBSTRING function in Sql Server

While trying to execute the below query

Declare @t table (id int, string varchar(1000))

INSERT INTO @t (id, string)
SELECT 1, 'zxzzxx,ppppbppp,trtrtr,tyyt,hgghh,fefew,rewr,rwerer' 

;WITH test (id, lft, rght, idx)
AS 
(
        SELECT t.id
                ,LEFT(t.string, CHARINDEX(', ', t.string) - 1)
                ,SUBSTRING(t.string, CHARINDEX(', ', t.string) + 2, DATALENGTH(t.string))
                ,0
        FROM @t t
        UNION ALL
        SELECT c.id
                ,CASE WHEN CHARINDEX(', ', c.rght) = 0 THEN c.rght ELSE LEFT(c.rght, CHARINDEX(', ', c.rght) - 1) END
                ,CASE WHEN CHARINDEX(', ', c.rght) > 0 THEN SUBSTRING(c.rght, CHARINDEX(', ', c.rght) + 2, DATALENGTH(c.rght)) 
                        ELSE '' END
                ,idx + 1
        FROM test c 
        WHERE DATALENGTH(c.rght) > 0
)

select id, lft from test 

I am getting the below error

Msg 537, Level 16, State 2, Line 8
Invalid 开发者_StackOverflow社区length parameter passed to the LEFT or SUBSTRING function.

but the same works for SELECT 1, 'the, quick, brown, fox, jumped, over, the, lazy, dog'

Please help

---

It seems to be a space missing between your words.

You are currently looking for charindex of ', ' not ','.

And the string does not have any match of ', '.

---

This Error message happens usually when you do these steps;

• When you use Substring, left, right functions.

  When you use CharIndex (used in the field, the selected word or word search, or the length of a character to be inadequate)

The return value is returned to each server in the query expression, the result of the transaction 0 (zero) returns, if error returns -1

This will not result in errors for the server to return a value of -1 or are compared objects.