StyleCleveland StyleCleveland - 2 years ago 116
SQL Question

Substring and Charindex - issues with minus operator

I am using SQL Server 2012. In the column

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

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:

substring(Project_name,(charindex(',',PROJECT_NAME,0)),((CHARINDEX(' v ',PROJECT_NAME)-

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 Source

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

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download