question

Naveen Kumar avatar image
Naveen Kumar 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.
substringcharindex
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image
Kev Riley answered
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?
5 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

the execution plan will tell you that
1 Like 1 ·
Ofcourse it is giving good results after including that condition, but my question is why is it raising errors without that condition
0 Likes 0 ·
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?
0 Likes 0 ·
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
0 Likes 0 ·
K Thank You Very Much
0 Likes 0 ·
HelloFOFO avatar image
HelloFOFO answered
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 ...
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.