Search code examples
sqlsql-serverpatindex

Invalid length parameter passed to the LEFT or SUBSTRING function 4

I realize there are similar questions here but none of them are exactly what I need. I have a SQL sproc that part of it executes a view. I have 3 databases that are identical in design and function but the data is different and I am getting this error on only one of them. If i take the view from the erroring DB and execute it on one of the others, it runs just fine. The weirdest part is that this view worked, then stopped, then fixed itself and started working again and when I started making changes to some data, it stopped again.

I have boiled it down to this specific line; 

Left(AO.Name,PATINDEX('%-%',AO.Name)-1) as ColumnName

The interesting part is if i change '%-% to be '%' the error goes away and my SPROC executes properly.

I have five questions;

  1. Why am i getting this error when I have the '%-%' but not '%'? Whats the difference and what kind of a change is made by using one vs the other?

  2. What does this error mean?

  3. Why would the exact same view work properly on two other identical databases but not this one?

  4. Why would this stop working after it was working, then somehow fix itself and then break itself again after some data manipulation?

  5. Is there a better method outside of PATINDEX that I could use?

Any help is appreciated. Thank you.

Solution

  • This is going to happen when there is no hyphen in the string.

    First, you can replace PATINDEX() with CHARINDEX() in this case:

    LEFT(AO.Name, CHARINDEX('-', AO.Name) - 1) as ColumnName

    That doesn't fix the problem, but it is simpler to follow.

    Then, the problem is that AO.Name has no hyphen. In that case PATINDEX() (and CHARINDEX() too) returns 0. You subtract 1 and the value is illegal as a second argument to LEFT().

    The easiest solution is to put a hyphen at the end:

    LEFT(AO.Name, CHARINDEX('-', AO.Name + '-') - 1) as ColumnName