getting error Invalid length parameter passed

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

getting error Invalid length parameter passed

Author

Topic

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2015-03-09 : 21:44:26

Hi,

I'm having problem running my query which i'm getting this error.
May i ask help on how to fix this error. thank you in advance.

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

Here is the code that i stuck up.

Substring(ins.itemid,11,CHARINDEX('-',ins.itemid+'-',11)-11) AS MODEL

Sample data of Itemid.

B300-4410-ONEMAX-U
B300-1725-XWHT-U
B300-3110-VT981-U
B300-1720-DRZRKCROBLK-U
B300-1720-DRDTHN32BLK-U

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2015-03-10 : 00:27:28

--sample data population
declare @tab table( itemid varchar(100))
insert @tab
SELECT 'B300-4410' union all
SELECT 'B300-4410-ONEMAX-U' union all
SELECT 'B300-1725-XWHT-U' union all
SELECT 'B300-3110-VT981-U' union all
SELECT 'B300-1720-DRZRKCROBLK-U' union all
SELECT 'B300-1720-DRDTHN32BLK-U'

SELECT Substring(ins.itemid,11,CHARINDEX('-',ins.itemid+'-',11)-11) AS MODEL
FROM @tab ins

Due to the above red marked data the below error occurs,
/*
Msg 537, Level 16, State 2, Line 9
Invalid length parameter passed to the LEFT or SUBSTRING function.
*/

Soution for the above issue is:

SELECT Substring(ins.itemid,11,CHARINDEX('-',ins.itemid+'-',11)-11) AS MODEL
FROM @tab ins
WHERE CHARINDEX('-',ins.itemid+'-',11)>11

--
Chandu

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2015-03-10 : 00:52:59

Hi Bandi,

I notice some of the string has space after the first hyphen. please see below. maybe this cause the error.
Try also this code AND LEN(ins.itemid) < 11 and the result is empty.

B300-4410-ONEMAX-U
B300- 1725-XWHT-U
B300-3110-VT981-U
B300-1720-DRZRKCROBLK-U
B300- 1720-DRDTHN32BLK-U

By the way, It is working when i added the this code. WHERE CHARINDEX('-',ins.itemid+'-',11)>11
Can you please give me a little bit of explanation of your code and how does it work. THank you.

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2015-03-10 : 02:22:23

The previous error is due to negative value for 3rd parameter of SUBSTRING function...
--run below SELECT, you will get know the reason
SELECT SUBSTRING( 'Bandi Chandu', 3, -5)

Msg 536, Level 16, State 1, Line 1
Invalid length parameter passed to the substring function.

Somehow the CHARINDEX() of your query (CHARINDEX('-',ins.itemid+'-',11)) returning less than 11; and then you are subtracting with 11; So the 3rd param value becomes negative

To resolve that issue,
I have added WHERE condition to process only the valid records

--
Chandu

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2015-03-10 : 03:52:32

Cool Bandi. thank you very much.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources