asked
Substring in where condition
SELECT COL1, COL2, CHARINDEX('-',COL2)-1 AS COL21, CHARINDEX('-',COL1)-1 AS COL11 FROM TABLE WHERE DBO.CountString(COL1,'-') > 1 AND CHARINDEX('-',COL2)-1 > 0 AND REVERSE(SUBSTRING(REVERSE(COL1),1,CHARINDEX('-',REVERSE(COL1))-1)) = LEFT(COL2,CHARINDEX('-',COL2)-1) ORDER BY COL1,COL2 This code is returning the error Msg 537, Level 16, State 2, Line 1 Invalid length parameter passed to the LEFT or SUBSTRING function. where as the same code after adding a condition in where clause it is executing. SELECT COL1, COL2, CHARINDEX('-',COL2)-1 AS COL21, CHARINDEX('-',COL1)-1 AS COL11 FROM TABLE WHERE DBO.CountString(COL1,'-') > 1 AND CHARINDEX('-',COL2)-1 > 0 AND CHARINDEX('-',COL1)-1 > 0 AND REVERSE(SUBSTRING(REVERSE(COL1),1,CHARINDEX('-',REVERSE(COL1))-1)) = LEFT(COL2,CHARINDEX('-',COL2)-1) ORDER BY COL1,COL2 EXECUTED SUCCESSFULLY I have used countstring(UDF) to get the COL1 values where the string having more than one specified character (like "-"). So based on this condition the COL1 does not have CHARINDEX('-',COL1)-1 > 0, but only with this condition in where clause it is executing successfully. please help me. Thanks in advance.
Adding the where clause `AND CHARINDEX('-',COL1)-1 > 0 ` is ensuring that the values are valid for processing in the substring clause as you have found out. Maybe there is an issue with the UDF. However why not include this where clause if it gives you 'good' values to substring? Without this you will get the error - how else do you want to handle this?
Ofcourse it is giving good results after including that condition, but my question is why is it raising errors without that condition
Without that condition, some rows with values that cause the error are being included - check the execution plan, the UDF may be being evaluated after the selection of base rows to work on. Or the UDF may be flawed?
Cant we predict which statement it is executing.. I mean the flow of conditions how it is considering. If any option like that pls let me know
Execute this SQL to verify whether u got some errors in the UDF(dbo.CountString) or not: SELECT COL1,DBO.CountString(COL1,'-') FROM Table WHERE CHARINDEX('-',REVERSE(COL1)) = 0 AND DBO.CountString(COL1,'-') > 1 If @@ROWCOUNT > 0 ,that is it ...
