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.