Invalid length parameter passed to the substring function.

Question

Post reply

Invalid length parameter passed to the substring function.

dba321

Hall of Fame

Points: 3917
More actions
February 13, 2007 at 10:46 am

#117744

This is driving me absolutley crazy. This function returns ProductIDs stripped out from a memo field based on a check on Product Type (which is also stripped for the checking) and returns a list of ProductIDs based on the type I'm checking for, in other words the @FileFormat
The error: Invalid length parameter passed to the substring function.
I am passing 2 types of ProductDescriptions to my function below. This function returns ProductIDs by Type, stripped out of a large varchar field. The problem I'm having is that I have 2 checks that check whether the ProductID is MP3 or WAV. The second check is failing.
The 2 types of possible incoming ProductDescriptions for example are:
'These fully-orchestrated royalty free music tracks invoke the spirit of some of the great themes from 1970"s and 1980"s television and film productions and offer majestic brass, string and guitar melodies that will make a memorable addition to projects as background music and production music. You can also purchase the individual tracks: 01. American Plains <a href="ProductInfo.aspx?ProductID=105234">MP3</a> | <a href="ProductInfo.aspx?ProductID=105235">WAV</a> 02. Sultry Summer Night <a href="ProductInfo.aspx?ProductID=105236">MP3</a> | <a href="ProductInfo.aspx?ProductID=105237">WAV</a> 03. Ocean Skyline <a href="ProductInfo.aspx?ProductID=105238">MP3</a> | <a href="ProductInfo.aspx?ProductID=105239">WAV</a> 04. Wistful Lover <a href="ProductInfo.aspx?ProductID=105240">MP3</a> | <a href="ProductInfo.aspx?ProductID=105241">WAV</a> 05. Final Choice <a href="ProductInfo.aspx?ProductID=105242">MP3</a> | <a href="ProductInfo.aspx?ProductID=105243">WAV</a> 06. Fun and Free <a href="ProductInfo.aspx?ProductID=105244">MP3</a> | <a href="ProductInfo.aspx?ProductID=105245">WAV</a> 07. Wayward Strangers <a href="ProductInfo.aspx?ProductID=105246">MP3</a> | <a href="ProductInfo.aspx?ProductID=105247">WAV</a> 08. Savored Moments <a href="ProductInfo.aspx?ProductID=105248">MP3</a> | <a href="ProductInfo.aspx?ProductID=105249">WAV</a> 09. Endless Searcher <a href="ProductInfo.aspx?ProductID=105250">MP3</a> | <a href="ProductInfo.aspx?ProductID=105251">WAV</a> 10. Bach Piano <a href="ProductInfo.aspx?ProductID=105252">MP3</a> | <a href="ProductInfo.aspx?ProductID=105253">WAV</a> 11. Fog Bound Mornings <a href="ProductInfo.aspx?ProductID=105254">MP3</a> | <a href="ProductInfo.aspx?ProductID=105255">WAV</a> '
OR
'Clapping percussion effects characterize this Hip Hop/Urban piece with train sound effects and strings. Styles: Dramatic,Reflective,Somber/dark If you like this track, you can save more than 70% by purchasing it on the following albums: <a href="ProductInfo.aspx?ProductID=106758">Hip-Hop / Urban</a> <a href="ProductInfo.aspx?ProductID=106763">Documentary, Film, Television, General Production - Volume 2</a> Click <a href="ProductInfo.aspx?ProductID=105747">here</a> for the WAV version of this track.'
The current function I'm trying to fix:
ALTER FUNCTION [dbo].[GetProductChildIDs]
(
 @ProductDescription varchar(5500),
 @FileFormat varchar(3)
)
RETURNS varchar(1000)
AS
BEGIN

Declare @Keyword varchar(30),
 @KeywordLen tinyint,
 @ProductID int,
 @Pos smallint,
 @StartPos smallint,
 @EndPos smallint,
 @Result varchar(1000),
 @valid int

SET @valid = 0
SET @Keyword = 'ProductInfo.aspx?ProductID='
SET @KeywordLen = LEN(@Keyword)
SET @Result = ''

SET @Pos = 1

WHILE @Pos > 0
 BEGIN
 SET @Pos = CHARINDEX(@Keyword, @ProductDescription, @Pos)
 If @Pos > 0
 BEGIN

 SET @StartPos = @Pos + @KeywordLen
 SET @EndPos = CHARINDEX('"', @ProductDescription, @StartPos + 1)

 IF SUBSTRING(@ProductDescription, @EndPos + 2, 3) = @FileFormat
 SET @valid = 1

 IF (len(@ProductDescription) - @StartPos) > 19
 IF SUBSTRING(@ProductDescription, @EndPos + 19, 3) = @FileFormat
 SET @valid = 1

 IF @valid = 1
 BEGIN
 SELECT @Result = @Result + SUBSTRING(@ProductDescription, @StartPos, @EndPos - @StartPos) + ','
 END
 SET @Pos = @EndPos + 1
 END
 END
 RETURN SUBSTRING(@Result,1,len(@Result)-1)
END
The following checks to see whether the ProductID is WAV or MP3 by checking certain chars after each sequence 'ProductInfo.aspx?ProductID='
IF
SUBSTRING(@ProductDescription, @EndPos + 2, 3) = @FileFormat - checks if it's a 'WAV' or 'MP3' based on the first type of incoming ProductDescription passed to this function at any time
IF SUBSTRING(@ProductDescription, @EndPos + 19, 3) = @FileFormat - checks to see if it's a 'WAV' or 'MP3' ProductID based on the second type of possible incoming ProductDescription.
I found out that the second check fails if there isn't 19 or more chars to check so I tried putting this in:
So I'm not sure how to handle the Nulls in the second IF statement if there are not enough chars to check in the loop for each SUBSTRING(@ProductDescription, @EndPos + 19, 3)

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Ken McKelvey SSCoach Points: 18856 More actions · Answer 1

Try declaring variables @WavPos and @MP3Pos and then replacing:

IF SUBSTRING(@ProductDescription, @EndPos + 2, 3) = @FileFormat

SET @valid = 1

IF (len(@ProductDescription) - @StartPos) > 19

IF SUBSTRING(@ProductDescription, @EndPos + 19, 3) = @FileFormat

SET @valid = 1

with:

SELECT @MP3Pos = CHARINDEX('MP3', @ProductDescription, @EndPos)

,@WavPos = CHARINDEX('WAV', @ProductDescription, @EndPos)

,@valid = 0

IF @WavPos > 0 AND (@WavPos < @MP3Pos OR @MP3Pos = 0) AND @FileFormat = 'WAV'

SET @valid = 1

IF @MP3Pos > 0 AND (@MP3Pos < @WAVPos OR @WAVPos = 0) AND @FileFormat = 'MP3'

SET @valid = 1

dba321 Hall of Fame Points: 3917 More actions · Answer 2

Interesting approach. Let me ponder that and try it. never thought about doing it this way.

dba321 Hall of Fame Points: 3917 More actions · Answer 3

Error: Invalid length parameter passed to the substring function.

Incoming ProductDescription is:

'These fully-orchestrated royalty free music tracks invoke the spirit of some of the great themes from 1970"s and 1980"s television and film productions and offer majestic brass, string and guitar melodies that will make a memorable addition to projects as background music and production music. You can also purchase the individual tracks: 01. American Plains <a href="ProductInfo.aspx?ProductID=105234">MP3</a> | <a href="ProductInfo.aspx?ProductID=105235">WAV</a> 02. Sultry Summer Night <a href="ProductInfo.aspx?ProductID=105236">MP3</a> | <a href="ProductInfo.aspx?ProductID=105237">WAV</a> 03. Ocean Skyline <a href="ProductInfo.aspx?ProductID=105238">MP3</a> | <a href="ProductInfo.aspx?ProductID=105239">WAV</a> 04. Wistful Lover <a href="ProductInfo.aspx?ProductID=105240">MP3</a> | <a href="ProductInfo.aspx?ProductID=105241">WAV</a> 05. Final Choice <a href="ProductInfo.aspx?ProductID=105242">MP3</a> | <a href="ProductInfo.aspx?ProductID=105243">WAV</a> 06. Fun and Free <a href="ProductInfo.aspx?ProductID=105244">MP3</a> | <a href="ProductInfo.aspx?ProductID=105245">WAV</a> 07. Wayward Strangers <a href="ProductInfo.aspx?ProductID=105246">MP3</a> | <a href="ProductInfo.aspx?ProductID=105247">WAV</a> 08. Savored Moments <a href="ProductInfo.aspx?ProductID=105248">MP3</a> | <a href="ProductInfo.aspx?ProductID=105249">WAV</a> 09. Endless Searcher <a href="ProductInfo.aspx?ProductID=105250">MP3</a> | <a href="ProductInfo.aspx?ProductID=105251">WAV</a> 10. Bach Piano <a href="ProductInfo.aspx?ProductID=105252">MP3</a> | <a href="ProductInfo.aspx?ProductID=105253">WAV</a> 11. Fog Bound Mornings <a href="ProductInfo.aspx?ProductID=105254">MP3</a> | <a href="ProductInfo.aspx?ProductID=105255">WAV</a> '

ALTER FUNCTION [dbo].[GetProductChildIDs]

(

@ProductDescription varchar(5500),

@FileFormat varchar(3)

)

RETURNS varchar(1000)

AS

BEGIN

Declare @Keyword varchar(30),

@KeywordLen tinyint,

@ProductID int,

@Pos smallint,

@StartPos smallint,

@EndPos smallint,

@Result varchar(1000),

@valid int,

@MP3Pos int,

@WavPos int

--@ProductDescription varchar(5500),

--@FileFormat varchar(3)

SET @valid = 0

SET @Keyword = 'ProductInfo.aspx?ProductID='

SET @KeywordLen = LEN(@Keyword)

SET @Result = ''

SET @Pos = 1

WHILE @Pos > 0

BEGIN

SET @Pos = CHARINDEX(@Keyword, @ProductDescription, @Pos)

If @Pos > 0

BEGIN

SET @StartPos = @Pos + @KeywordLen

SET @EndPos = CHARINDEX('"', @ProductDescription, @StartPos + 1)

SELECT @MP3Pos = CHARINDEX('MP3', @ProductDescription, @EndPos)

,@WavPos = CHARINDEX('WAV', @ProductDescription, @EndPos)

,@valid = 0

IF @WavPos > 0 AND (@WavPos < @MP3Pos OR @MP3Pos = 0) AND @FileFormat = 'WAV'

SET @valid = 1

IF @MP3Pos > 0 AND (@MP3Pos < @WAVPos OR @WAVPos = 0) AND @FileFormat = 'MP3'

SET @valid = 1

IF @valid = 1

BEGIN

SELECT @Result = @Result + SUBSTRING(@ProductDescription, @StartPos, @EndPos - @StartPos) + ','

END

SET @Pos = @EndPos + 1

END

RETURN SUBSTRING(@Result,1,len(@Result)-1)

END

dba321 Hall of Fame Points: 3917 More actions · Answer 4

but does your check account for the second type of description, where i'm checking for the word WAV or MP3 in the following string? 'for the WAV version of this track.'

I don't see where it's doing that. Looks to me like it's just checking for WAV or MP3 baed on the first description format

dba321 Hall of Fame Points: 3917 More actions · Answer 5

I don't understand why you're doing this

@WavPos

< @MP3Pos OR @MP3Pos = 0

The whole idea is to check whether after each instance of ProductID=, after the productID, if it's a WAV or MP3 based on either of the two possible strings that contain WAV or MP3 here:

'MP3</a>'

'WAV</a>'

'for the MP3 version of this track.'

'for the WAV version of this track.'

This check already worked fine for the first type of product description where it ripped out and checked whether 'MP3</a>' or 'WAV</a>':

IF SUBSTRING(@ProductDescription, @EndPos + 2, 3) = @FileFormat

dba321 Hall of Fame Points: 3917 More actions · Answer 6

I am thinking this will also not work:

SELECT

@MP3Pos = CHARINDEX('MP3', @ProductDescription, @EndPos)

Because consider this example incoming ProdDesc:

'Clapping percussion effects characterize this Hip Hop/Urban piece with train sound effects and strings. Styles: Dramatic,Reflective,Somber/dark If you like this track, you can save more than 70% by purchasing it on the following albums: <a href="ProductInfo.aspx?ProductID=106758">Hip-Hop1 / Urban</a> <a href="ProductInfo.aspx?ProductID=106751">Hip-Hop2 / Urban</a> Click <a href="ProductInfo.aspx?ProductID=105747">here</a> for the WAV version of this track.<a href="ProductInfo.aspx?ProductID=106750">Hip-Hop / Urban</a> <a href="ProductInfo.aspx?ProductID=106763">Documentary, Film, Television, General Production - Volume 2</a> Click <a href="ProductInfo.aspx?ProductID=105748">here</a> for the WAV version of this track.'

I would think your line above may take for example 106758 as a WAV since it would eventually find 'for the WAV version ' further down the line in the same loop iteration. The rule of thumb for the description above is, if there is no string 'for the WAV version of this track.' right after a ProductID, then it's considered an MP3 product by default. So therefore for this poriton of the above ><a href="ProductInfo.aspx?ProductID=106758">Hip-Hop1, the ProductID 106758 is an MP3, while products 105747 & 105748 are WAVs.

dba321 Hall of Fame Points: 3917 More actions · Answer 7

so going back to my example again now (updated):

Declare @Keyword varchar(30),

@KeywordLen tinyint,

@ProductID int,

@Pos smallint,

@StartPos smallint,

@EndPos smallint,

@Result varchar(1000),

@valid int

SET @valid = 0

SET @Keyword = 'ProductInfo.aspx?ProductID='

SET @KeywordLen = LEN(@Keyword)

SET @Result = ''

SET @Pos = 1

WHILE @Pos > 0

BEGIN

SET @Pos = CHARINDEX(@Keyword, @ProductDescription, @Pos)

If @Pos > 0

BEGIN

SET @StartPos = @Pos + @KeywordLen

SET @EndPos = CHARINDEX('"', @ProductDescription, @StartPos + 1)

IF SUBSTRING(@ProductDescription, @EndPos + 2, 3) = @FileFormat

SET @valid = 1

IF SUBSTRING(@ProductDescription, @EndPos + 19, 3) <> NULL

IF SUBSTRING(@ProductDescription, @EndPos + 19, 3) = @FileFormat

SET @valid = 1

IF @valid = 1

BEGIN

SELECT @Result = @Result + SUBSTRING(@ProductDescription, @StartPos, @EndPos - @StartPos) + ','

END

SET @Pos = @EndPos + 1

END

print SUBSTRING(@Result,1,len(@Result)-1)

END

I would think this check would take care of it

IF SUBSTRING(@ProductDescription, @EndPos + 19, 3) <> NULL

dba321 Hall of Fame Points: 3917 More actions · Answer 8

Ok, I tested this here

Declare @Keyword varchar(30),

@KeywordLen tinyint,

@ProductID int,

@Pos smallint,

@StartPos smallint,

@EndPos smallint,

@Result varchar(1000),

@valid int,

@MP3Pos int,

@WavPos int,

@ProductDescription varchar(5500),

@FileFormat varchar(3)

SET @valid = 0

SET @Keyword = 'ProductInfo.aspx?ProductID='

SET @KeywordLen = LEN(@Keyword)

SET @Result = ''

SET @ProductDescription = 'Clapping percussion effects characterize this Hip Hop/Urban piece with train sound effects and strings. Styles: Dramatic,Reflective,Somber/dark If you like this track, you can save more than 70% by purchasing it on the following albums: <a href="ProductInfo.aspx?ProductID=106758">Hip-Hop / Urban</a> <a href="ProductInfo.aspx?ProductID=106763">Documentary, Film, Television, General Production - Volume 2</a> Click <a href="ProductInfo.aspx?ProductID=105747">here</a> for the WAV version of this track.'

SET @FileFormat = 'WAV'

SET @Pos = 1

WHILE @Pos > 0

BEGIN

SET @Pos = CHARINDEX(@Keyword, @ProductDescription, @Pos)

If @Pos > 0

BEGIN

SET @StartPos = @Pos + @KeywordLen

SET @EndPos = CHARINDEX('"', @ProductDescription, @StartPos + 1)

IF SUBSTRING(@ProductDescription, @EndPos + 2, 3) = @FileFormat

SET @valid = 1

print SUBSTRING(@ProductDescription, @EndPos + 19, 3)

IF SUBSTRING(@ProductDescription, @EndPos + 19, 3) <> NULL

IF SUBSTRING(@ProductDescription, @EndPos + 19, 3) = @FileFormat

SET @valid = 1

IF @valid = 1

BEGIN

SELECT @Result = @Result + SUBSTRING(@ProductDescription, @StartPos, @EndPos - @StartPos) + ','

END

SET @Pos = @EndPos + 1

END

print SUBSTRING(@Result,1,len(@Result)-1)

which does return this

a><

, T

WAV <--- this is what I want! Says the last one is a WAV, correct!

which is right so why the original error still? I found that it's actually talking about this line here finally:

Invalid length parameter passed to the substring function.

Talks about

Line 70 which is

SELECT @Result = @Result + SUBSTRING(@ProductDescription, @StartPos, @EndPos - @StartPos) + ','

dba321 Hall of Fame Points: 3917 More actions · Answer 9

oK, looks like I'm slowy working my way through this one myself. I found out that no matter what, after the IF statements, @valid is never being set to true

IF SUBSTRING(@ProductDescription, @EndPos + 2, 3) = @FileFormat

BEGIN

SET @valid = 1

END

print @valid

print SUBSTRING(@ProductDescription, @EndPos + 19, 3)

IF SUBSTRING(@ProductDescription, @EndPos + 19, 3) <> NULL

IF SUBSTRING(@ProductDescription, @EndPos + 19, 3) = @FileFormat

BEGIN

SET @valid = 1

END

print @valid

IF @valid = 1

BEGIN

PRINT @ProductDescription

PRINT @StartPos

PRINT @EndPos

SELECT @Result = @Result + SUBSTRING(@ProductDescription, @StartPos, @EndPos - @StartPos) + ','

END

dba321 Hall of Fame Points: 3917 More actions · Answer 10

Ok, figured it out on my own:

Declare @Keyword varchar(30),

@KeywordLen tinyint,

@ProductID int,

@Pos smallint,

@StartPos smallint,

@EndPos smallint,

@Result varchar(1000),

@ResultToReturn varchar(1000),

@valid int,

@MP3Pos int,

@WavPos int

SET @valid = 0

SET @Keyword = 'ProductInfo.aspx?ProductID='

SET @KeywordLen = LEN(@Keyword)

SET @Result = ''

SET @ResultToReturn = ''

SET @Pos = 1

WHILE @Pos > 0

BEGIN

SET @Pos = CHARINDEX(@Keyword, @ProductDescription, @Pos) -- returns starting index of ProductInfo.aspx?ProductID= occurance

If @Pos > 0

BEGIN

SET @StartPos = @Pos + @KeywordLen -- returns start to be the end of ProductInfo.aspx?ProductID=

SET @EndPos = CHARINDEX('"', @ProductDescription, @StartPos + 1) -- sets start to be after =

IF SUBSTRING(@ProductDescription, @EndPos + 2, 3) = @FileFormat

BEGIN

SET @valid = 1

END

IF SUBSTRING(@ProductDescription, @EndPos + 19, 3) = @FileFormat

BEGIN

SET @valid = 1

END

IF @valid = 1

BEGIN

SELECT @Result = @Result + SUBSTRING(@ProductDescription, @StartPos, @EndPos - @StartPos) + ','

END

SET @Pos = @EndPos + 1

END

IF len(@Result) > 6

SET @ResultToReturn = @ResultToReturn + @Result

RETURN @ResultToReturn

END

dba321 Hall of Fame Points: 3917 More actions · Answer 11

dba321

Hall of Fame

Points: 3917

February 13, 2007 at 2:02 pm

#689574

thanks for your pointers though!

regards