Spliting Full name into First and Last name 1

[JazzMaan]

I neet do divide Full name into First and Last name. They are seperated by space.

I have tried the following which for the first name but not last name.

select full_name,
LEFT(rtrim(ltrim(full_name)), CHARINDEX(' ', full_name) ) as First_name,
RIGHT(rtrim(ltrim(full_name)), CHARINDEX(' ', rtrim(ltrim(full_name)) )) as Last_name
FROM managers


Need to get something like following, which sql dosent accept
-- substring(full_name, (CHARINDEX(' ', full_name)), (len(full_name) - CHARINDEX(' ', full_name)) )

 

[JayKusch]

SELECT SUBSTRING(Full_Name,1,PatIndex(' ',Full_Name)) AS FirstName, SUBSTRING(Last_Name,PatIndex(' ',Full_Name),LEN(Full_Name)) AS LastName

Thanks

J. Kusch

 

[JazzMaan]

Thanks Works but with a little modification of '% %'


SELECT SUBSTRING(Full_Name,1,PatIndex('% %',Full_Name)) AS FirstName,
SUBSTRING(Last_Name,PatIndex('% %',Full_Name),LEN(Full_Name)) AS LastName

 

[JazzMaan]

A little more modification to get rid of space in first and last name & If only First name is there.

case
when PatIndex('% %',FULL_NAME) = 0 then FULL_NAME
else SUBSTRING(FULL_NAME,1,PatIndex('% %',FULL_NAME)-1) end,
case
when PatIndex('% %',FULL_NAME) <> 0 then
SUBSTRING(FULL_NAME,PatIndex('% %',FULL_NAME)+1,LEN(FULL_NAME)) end

 

[JayKusch]

At least we got it though!

Thanks

J. Kusch