I am trying to retrieve multiple Columns from a DE and split a column into multiple columns and insert into another DE along with the original column.

Ex : I have a DE-1 with Full name, Email and Phone Number as columns. I need to split the Full Name into 2 as First name and Last Name and Store it in a DE-2 along with Full name, Email and Phone Number.

How do i do this using an automation studio and SQL query in SFMC.

2 Answers 2


You basically need to find where a space is in the full name and write whatever is before the space into the first name column and whatever is after the space to the last name column. This should do the trick:

SELECT SUBSTRING([full name], 1, CHARINDEX(' ', [full name]) - 1) AS Firstname,     
       SUBSTRING([full name],
                 CHARINDEX(' ', [full name]) + 1,
                 LEN([full name]) - CHARINDEX(' ', [full name])) AS Lastname
FROM de1

Here is the answer you would like to use the same SQL functions

 SELECT EmailAddress,
      SUBSTRING(Fullname, 1, CHARINDEX(' ', FullName) - 1) AS FirstName,
      SUBSTRING(FullName, CHARINDEX(' ', FullName) + 1, 8000) AS LastName
    FROM [Your Data Extension Name] 

When you will select the data extension in the next step it will automatically use the FirstName and Last name as target columns.

NOTE: It's considering your First name is separated by space

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .