SQL Server Substring

Question

When the following query is executed, if it does not start, if the second substring operator (commented here) is not relevant. What is going on here, what am I missing?

Uses 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 

Answer 1

Charindex(' ', Contactname) - 1

Returns -1if it Contactnamedoes not contain a space. This is an invalid length parameter.

There must be Contactnameone that invokes the expression Substring, but it is filtered using JOIN.

Presumably, the calculation scalar shifts between the two plans and can be evaluated after joining when you have this line without comment.

. SQL Server .

Charindex

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

Answer 2

. , .

Common Table, , charindex() .

, () .

-- Use the sample db
use [Northwind]
go

-- Watch out for null & one name
;
with cteContactsOrders
as
(
SELECT 
  Contactname as FullName,
  Substring(IsNull(Contactname, ''), 1, 4) as FirstFour,
  Charindex(' ', IsNull(Contactname, '')) as Pos,
  Phone, 
  Orderid,
  Orderdate 
FROM   
  customers as c
INNER JOIN 
  orders as o
ON 
  c.Customerid = o.Customerid 
)
select 
    co.*,

    case 
   when Pos > 0 then substring(FullName, 1, Pos-1) 
   when Pos = 0 and len(ltrim(rtrim(FullName))) > 0 then FullName
   else ''
    end as FirstName,

    case 
       when Pos > 0 then substring(FullName, Pos+1, len(FullName) - Pos) 
       else ''
    end as LastName
from 
    cteContactsOrders co

SQL Server 2014 CTP2.