Substring gives error

Question

Post reply

Substring gives error

lucky-80472

SSCertifiable

Points: 7793
More actions
July 29, 2008 at 4:53 pm

#210601

Hello,
I'm trying to build a logic around the following sample records in order to get the deptid by way of
Substring function. But somehow it is erroring out saying that "Invalid length parameter is passed to the function".
http://website.net/trimmed-Jacket.aspx?yyy=137668&DeptId=16465&TypeId=1&Type=0&viewall=1
http://www.website.com/Elastic-Triple-Pleated.aspx?yyy=96561&DeptId=13898&TypeId=1&Type=0
http://www.website.com/Double-Pleated-Stretch.aspx?yyy=111616&DeptId=14376&TypeId=1&Type=0
http://www.website.com/Product/AltPopUp.aspx?yyy=120767&DeptId=13849&TypeId=1&Type=0&NotFound=1&za=1
http://www.website.com/DetailNotFound.aspx?yyy=103770&DeptId=13849&TypeId=1&Type=0&NotFound=1
http://www.website.com/DetailNotFound.aspx?yyy=69371&DeptId=13848&TypeId=1&Type=0&viewall=1&NotFound=1
http://www.website.com/Green-Living-Cotton-bed.aspx?yyy=127553&DeptId=15184&TypeId=1&Type=0
http://www.website.com/Suede-Gusseted-Pads.aspx?yyy=101540&DeptId=15222&typeid=1&pref=ps&Type=G
http://www.website.com/Beaded-Dress.aspx?yyy=113619&DeptId=14014&typeid=1&ViewAll=1&id=017&location_id=06&Wmc_id=k65590&mid=j16309379
This is the substring statement
substring('http://website.net/trimmed-Jacket.aspx?yyy=137668&DeptId=16465&TypeId=1&Type=0&viewall=1', (len('DeptId=') + CHARINDEX('DeptId=', 'http://website.net/trimmed-Jacket.aspx?yyy=137668&DeptId=16465&TypeId=1&Type=0&viewall=1')), (CHARINDEX('&TypeId=', 'http://website.net/trimmed-Jacket.aspx?yyy=137668&DeptId=16465&TypeId=1&Type=0&viewall=1') - len('DeptId=') - CHARINDEX('DeptId=', 'http://website.net/trimmed-Jacket.aspx?yyy=137668&DeptId=16465&TypeId=1&Type=0&viewall=1')))
I would appreciate if one of you can point me to the error.
Thanks

Lucky

Viewing 6 posts - 1 through 5 (of 5 total)

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

antonio.collins SSCrazy Eights Points: 9600 More actions · Answer 1

this suggestion addresses your need, not the error.

declare @Url table ( url varchar(1024 ))

insert into @Url

select 'http://website.net/trimmed-Jacket.aspx?yyy=137668&DeptId=16465&TypeId=1&Type=0&viewall=1'

union select 'http://www.website.com/Elastic-Triple-Pleated.aspx?yyy=96561&DeptId=13898&TypeId=1&Type=0'

union select 'http://www.website.com/Double-Pleated-Stretch.aspx?yyy=111616&DeptId=14376&TypeId=1&Type=0'

union select 'http://www.website.com/Product/AltPopUp.aspx?yyy=120767&DeptId=13849&TypeId=1&Type=0&NotFound=1&za=1'

union select 'http://www.website.com/DetailNotFound.aspx?yyy=103770&DeptId=13849&TypeId=1&Type=0&NotFound=1'

union select 'http://www.website.com/DetailNotFound.aspx?yyy=69371&DeptId=13848&TypeId=1&Type=0&viewall=1&NotFound=1'

union select 'http://www.website.com/Green-Living-Cotton-bed.aspx?yyy=127553&DeptId=15184&TypeId=1&Type=0'

union select 'http://www.website.com/Suede-Gusseted-Pads.aspx?yyy=101540&DeptId=15222&typeid=1&pref=ps&Type=G'

union select 'http://www.website.com/Beaded-Dress.aspx?yyy=113619&DeptId=14014&typeid=1&ViewAll=1&id=017&location_id=06&Wmc_id=k65590&mid=j16309379'

union select 'http://this.example.net/hasNoDeptId.aspx'

select U.url, substring( A.item, charindex( '=', A.item + '=' ) + 1, 999 ) as DeptId

from @Url U cross apply global.dbo.fListToVarchars( U.url, '&' ) as A

where A.item like 'DeptId=%'

as you can see, I find fListToVarchars() extremely useful.

Steve Jones - SSC Editor SSC Guru Points: 736468 More actions · Answer 2

You need to break out each part of the substring (len, charindex, etc) and see what values they are returning. That will help you find the error.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

Antonio's method is probably cleanest but if you want to pursue the method which you started with, then this will help.

DECLARE @LongStr VARCHAR(200), @ShortStr VARCHAR(200)

SET @LongStr = 'http://website.net/trimmed-Jacket.aspx?yyy=137668&DeptId=16465&TypeId=1&Type=0&viewall=1'

SET @ShortStr = 'DeptId='

/*

SELECT startpos = (LEN(@ShortStr) + CHARINDEX(@ShortStr,@LongStr)) -- 58

SELECT strlength = (CHARINDEX(@ShortStr, @LongStr) - len(@ShortStr) - CHARINDEX(@ShortStr,@LongStr)) -- -7 error!

-- query with error

SELECT substring(@LongStr,

(len(@ShortStr) + CHARINDEX(@ShortStr,@LongStr)),

(CHARINDEX(@ShortStr, @LongStr) - len(@ShortStr) - CHARINDEX(@ShortStr,@LongStr)))

*/

-- return the DeptId value with trailing characters up to max length of DeptID, say 10 chars

SELECT SUBSTRING(@LongStr, CHARINDEX(@ShortStr, @LongStr) + LEN(@ShortStr), 10)

-- find position of the terminating ampersand

SELECT CHARINDEX('&', SUBSTRING(@LongStr, CHARINDEX(@ShortStr, @LongStr) + LEN(@ShortStr), 10))-1

-- put it all together

SELECT LEFT(

SUBSTRING(@LongStr, CHARINDEX(@ShortStr, @LongStr) + LEN(@ShortStr), 10),

CHARINDEX('&', SUBSTRING(@LongStr, CHARINDEX(@ShortStr, @LongStr) + LEN(@ShortStr), 10))-1)

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

lucky-80472 SSCertifiable Points: 7793 More actions · Answer 4

lucky-80472

SSCertifiable

Points: 7793

July 30, 2008 at 10:07 am

#850653

Thanks to everyone who helped.

Lucky

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

Did you find a useful resolution for this problem?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden