Wednesday, March 28, 2012

invalied length parameter passed the substring function

Does anyone know what the above error message means?What length did you pass to substring?|||What do you mean, what length...|||From the SQL Server Books Online (a.k.a. BOL):

SUBSTRING
Returns part of a character, binary, text, or image expression. For more information about the valid Microsoft SQL Server data types that can be used with this function, see Data Types.

Syntax
SUBSTRING ( expression , start , length )

Arguments
....|||DECLARE @.x int, @.y int, @.z varchar(8000)

-- This works
SELECT @.x = 47, @.y = 22, @.z = 'Even if God Almighty, points his finger at us, It just Doesnt matter'
SELECT SUBSTRING(@.z,@.x,@.y)

-- This works in SQL Server...DB2's head would explode though since it's out of range
SELECT @.x = 47, @.y = 8000
SELECT SUBSTRING(@.z,@.x,@.y)

-- Same as the above...really doesn't make a whole lot of sense though
SELECT @.x = 47, @.y = 0
SELECT SUBSTRING(@.z,@.x,@.y)

-- So the Only thing that doesn't work is a negative...but the way sql works...probably could have allowed it
-- Would have been neat to go to the left in the string for negative nymbers
SELECT @.x = 47, @.y = -1
SELECT SUBSTRING(@.z,@.x,@.y)

EDIT: Oh, most likely you have code that's deriving the length..post the code and we can figure it out...

No comments:

Post a Comment