sgmunson (5/27/2015)

---

DBA2015 (5/27/2015)

---

sgmunson (5/26/2015)

---

I suspect that the original poster meant to use FROM in place of WHERE, but given the code, I'm fairly sure that there's a much easier way to code this update. Yes, the column names are horrific, but that's not necessarily a total disaster. Take a look at the following and see if it might be a more practical option:

UPDATE CSC

SET CSC.Lightsonlongname = L.[Name (Username):]

FROM dbo.CardioStaffClinical AS CSC

INNER JOIN lightson AS L

ON CSC.Forename = SUBSTRING(SUBSTRING(L.[Name (Username):], (CHARINDEX(' , ', L.[Name (Username):], 1) + 3), 50), 1,

CHARINDEX(' ', SUBSTRING(L.[Name (Username):], (CHARINDEX(' , ', L.[Name (Username):], 1) + 3), 50), 1) - 1)

AND CSC.[Surname ] = SUBSTRING(L.[Name (Username):], 1, CHARINDEX(' , ', L.[Name (Username):], 1) - 1)

Hi Steve,

When I run the query I am getting:

Msg 537, Level 16, State 3, Line 1

Invalid length parameter passed to the LEFT or SUBSTRING function.

The statement has been terminated.

Hope you can help

I had assumed your existing query was operational, but it now appears that it is not. Without data to work with, it would be difficult to determine where the problem is. However, clearly, one of the string functions is getting an invalid value, so you might want to expose the values that are being supplied, and check for negative numbers or possibly 0, where they don't belong. Once you know WHY the problem occurs, you can determine the appropriate replacement logic for that query. ChrisM has a good suggestion...