Showing posts with label invalied. Show all posts
Showing posts with label invalied. Show all posts

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...