Split fullName into first- and lastname in SQL, Excel and SSIS
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.
** This will be depricated as soon as the =TEXTSPLIT(), =TEXTAFTER() and =TEXTBEFORE() is rolled out to Office365. These listed functions will 100% replace the LEFT/RIGHT when doing this. **
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
DECLARE @FullName nvarchar(50) = 'Test User Testing'; SELECT FullName = @FullName ,FirstName = LTRIM(RTRIM(SUBSTRING(@FullName, 1, CHARINDEX(' ', @FullName)))) ,LastName = LTRIM(RTRIM(SUBSTRING(@FullName, CHARINDEX(' ', @FullName) + 1, 8000)))
SSIS Derived Column
Firstname: LEFT([FIELD],FINDSTRING([FIELD]," ",1) - 1) Lastname: RIGHT([FIELD],LEN([FIELD]) - FINDSTRING([FIELD]," ",1))