CODEWITHSUNDEEP
sqlsql-serversubstring
user3333669
user3333669

Reputation: 41

SQL Server Substring

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

Upvotes: 4

Views: 2796

Answers (2)

CRAFTY DBA
CRAFTY DBA

Reputation: 14915

You need to watch out for the negative cases. A null value, a empty string, or a one name person.

I used a Common Table Expression since I did not want the charindex() function all over the place.

Also, your first substring() did not substract the correct amount of characters.

-- 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

The output on SQL Server 2014 CTP2.

enter image description here

Upvotes: 0

Martin Smith
Martin Smith

Reputation: 452988

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)

Upvotes: 5

Related Questions