Geek Answers Handbook

Invalid length parameter passed to LEFT or SUBSTRING function on Sql server

When trying to execute the following 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 @tt 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 get the error below

 Msg 537, Level 16, State 2, Line 8 Invalid length parameter passed to the LEFT or SUBSTRING function.

but the same thing works for SELECT 1, "fast, brown, fox, jumping, over, lazy, doggy"

Please, help

+4
source share
2 answers

There seems to be no space between your words.

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

And the string does not have a matching ', ' .

+3
source

This error message usually occurs when you do these steps;

  • When you use Substring , left , right functions.

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

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

This will not cause errors for the server to return -1 or compare objects.

+2
source

More articles:


All Articles