SQL Server Substring

Question

When the following query is executed if fails to run unless the 2nd substring statement (commented out here) is uncommmented. What is going on here that I am missing? Uses the Northwind database ``` SELECT Substring(Contactname, Charindex(' ', Contactname) + 1, Len(Contactname))AS LastName, Substring(Contactname, 1, Charindex(' ', Contactname) - 1) AS FirstName1 --, substring(ContactName, 1, 4) AS FirstName2 -- if this line is commented out then the query crashes with the error msg --Invalid length parameter passed to the LEFT or SUBSTRING function. , Phone, Orderid, Orderdate FROM customers INNER JOIN orders ON customers.Customerid = orders.Customerid ```

Accepted Answer

```
Charindex(' ', Contactname) - 1
```

Returns `-1` if `Contactname` does not contain a space. This is an invalid length parameter.

There must be a `Contactname` that causes the `Substring` expression to fail but that is filtered out by the `JOIN`.

Presumably the compute scalar shifts around between the two plans and happens to be evaluated after the join when you have that line uncommented.

See SQL Server should not raise illogical errors for some discussion on this type of issue.

A way around this would be to append a space to the input to `Charindex`

```
Substring(Contactname, 1, Charindex(' ', Contactname + ' ' ) - 1)
```

SQL Server Substring

Solution

Problem