SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 ERROR: Invalid length parameter passed to the LEFT
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

JR83
Starting Member

South Africa
31 Posts

Posted - 05/21/2013 :  07:12:54  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 05/21/2013 :  07:14:32  Show Profile  Reply with Quote
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
Go to Top of Page

JR83
Starting Member

South Africa
31 Posts

Posted - 05/21/2013 :  07:23:32  Show Profile  Reply with Quote
should the values match? im sorry i dont understand
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 05/21/2013 :  07:29:27  Show Profile  Reply with Quote
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
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2223 Posts

Posted - 05/21/2013 :  07:39:11  Show Profile  Reply with Quote
-- 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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 05/21/2013 :  07:42:02  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000