This is something I find myself doing over and over and over again, when importing data to CRM, seeing as CRM wants the contacts as Firstname and Lastname. And the data I receive is very rarely in that condition. So I find myself having to split the column fairly often – and I always forget how to do it, so I have to look it up.
For that reason, I have collected three methods for the split: One for SQL, one for Excel and one for Derived Column in SSIS.
Firstname =LEFT(A2,FIND(" ",A2,1)-1) =VENSTRE(A2;FIND(" ";A2;1)-1) //Danish Excel version Lastname =RIGHT(A2,LEN(A2)-FIND(" ",A2,1)) =HØJRE(A2;LÆNGDE(A2)-FIND(" ";A2;1)) //Danish Excel version
SELECT [fullname] ,LTRIM(RTRIM(SUBSTRING(fullname, 1, CHARINDEX(' ', fullname)))) AS FirstName ,LTRIM(RTRIM(SUBSTRING(fullname, CHARINDEX(' ', fullname) + 1, 8000))) AS LastName FROM [STAGE_TABLES]
SSIS Derived Column
Firstname: LEFT([FIELD],FINDSTRING([FIELD]," ",1) - 1) Lastname: RIGHT([FIELD],LEN([FIELD]) - FINDSTRING([FIELD]," ",1))