ERROR: Invalid length parameter passed to the LEFT

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

General SQL Server Forums

New to SQL Server Programming

ERROR: Invalid length parameter passed to the LEFT

Author

Topic

JR83
Starting Member

31 Posts

Posted - 2013-05-21 : 07:12:54

ERROR: Invalid length parameter passed to the LEFT or SUBSTRING function.

WHEN RUNNING MY QUERY - LINE 1.

EG: Select Case When CHARINDEX('Result: ', AMGR_Notes.Textcol) > CHARINDEX('Subject: ',AMGR_Notes.TextCol)

I dont want to pull negative values

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-21 : 07:14:32

the issue is you've values without pattern 'Result:' ,'Subject:' etc in strings

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

JR83
Starting Member

31 Posts

Posted - 2013-05-21 : 07:23:32

should the values match? im sorry i dont understand

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-21 : 07:29:27

The issue is you've an expression involving CHARINDEX() logic somewhere and for values not having the serach patterns it returns 0 as value causing overall expression to give a negative result

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-05-21 : 07:39:11

-- see this illustration
DECLARE @Product TABLE(product_id INT, product_name VARCHAR(30))
INSERT INTO @Product
SELECT 1, 'barrings' union all
SELECT 2, 'wheel rim' union all
SELECT 3, 'Test LEFT() Subject: rim' union all
SELECT 4, 'rodd'

SELECT LEFT(product_name, CHARINDEX('Subject: ',product_name)-1 )
--,SUBSTRING(product_name, 1, CHARINDEX('Subject: ',product_name)-1)
FROM @Product
--WHERE CHARINDEX('Subject: ',product_name) >0

You will get error because of CHARINDEX('Subject: ',product_name)-1 becomes -ve value

Now run above code with WHERE condition.... See the result

--
Chandu

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-21 : 07:42:02

and one way to avoid this error is to do a small hack like below so that it always works


SELECT LEFT(product_name, CHARINDEX('Subject: ',product_name + 'Subject: ')-1 )
--,SUBSTRING(product_name, 1, CHARINDEX('Subject: ',product_name + 'Subject: ')-1)
FROM @Product

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources