query to divide the column fullname into firstName and lastName columns returned

Viewed 43

I am using Sql Server. I am trying to write an sql query to divide the column named fullName into firstName and lastName columns. but I keep on getting this error:

  Msg 537, Level 16, State 2, Line 1
  Invalid length parameter passed to the LEFT or SUBSTRING function.

This is my query:

 SELECT  CASE
      WHEN (LEN (FullName) - LEN (REPLACE (FullName, ' ', '')) + 1) > 0
      THEN
         Substring (FullName, 1, Charindex (' ', FullName) - 1)
      ELSE
         ''
   END AS FirstName,      
   CASE
      WHEN (LEN (FullName) - LEN (REPLACE (FullName, ' ', '')) + 1) > 0
       THEN REVERSE(SUBSTRING(REVERSE(FullName),
                   1,
                   CHARINDEX(' ', REVERSE(FullName)) - 1))
       ELSE FullName
   END AS LastName,email, c.customerid
FROM            Customer c

What can be causing this error ? I have verified that there are no fullname records that are empty '' or null. But fullname could hold just a firstname if someone forgot to add their lastname - I am not sure if this could be the issue ?

If I remove the +1:

 WHEN (LEN (FullName) - LEN (REPLACE (FullName, ' ', ''))) > 0 

Then it returns results - but the firstname and lastname columns are both empty even though fullname actually contains the first name but only the first name - for instance 'Seyhan'. How can I fix it so that at least it returns the firstname ?

0 Answers
Related