StyleCleveland StyleCleveland - 6 months ago 23
SQL Question

Substring and Charindex - issues with minus operator

I am using SQL Server 2012. In the column

PROJECT_NAME
I have line items, all with the same format, that look like this:

PROJECT_NAME
--------------
Caulk, Norman v BPI
Caulk, Norman v BWD
Carper, Robert v ECH


I am trying to extract the first name (second name in the text string) and am using this query:

select
substring(Project_name,(charindex(',',PROJECT_NAME,0)),((CHARINDEX(' v ',PROJECT_NAME)-
(charindex(',',PROJECT_NAME)))))
from RPT_PROJ_MAIN pm


When I run this query I get the following error:
Invalid length parameter passed to the LEFT or SUBSTRING function.


I have isolated all of the different expressions and they all work fine on their own. If I replace the minus operator with a
+
then the query runs fine and I cannot figure out why?

Answer

That means that despite you saying that all rows have the same format some of them don't.

Specifically if a value has no v <something> at the end you'll get exactly that error, because the third parameter to SUBSTRING() function will have a negative value

Here is SQLFiddle demo

Comments