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.

Excel:

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

SQL query:

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))

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *