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.
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 MVPhttp://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 MVPhttp://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 illustrationDECLARE @Product TABLE(product_id INT, product_name VARCHAR(30))INSERT INTO @ProductSELECT 1, 'barrings' union allSELECT 2, 'wheel rim' union allSELECT 3, 'Test LEFT() Subject: rim' union allSELECT 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) >0You will get error because of CHARINDEX('Subject: ',product_name)-1 becomes -ve valueNow 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 worksSELECT LEFT(product_name, CHARINDEX('Subject: ',product_name + 'Subject: ')-1 )--,SUBSTRING(product_name, 1, CHARINDEX('Subject: ',product_name + 'Subject: ')-1)FROM @Product ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|
|
|