Changing the order of a name - from surname, first name to first name, surname - can it be done?

DannyLewis
DannyLewis
in Dataflows

Hi, 

 

I've got a dataset that has the first name and surname comma separated but with surname first ie:

 

Lewis, Danny

 

Whereas I ideally want 

 

Danny Lewis 

 

Is this possible in Beast Mode? If not how would I transform at ETL level? 

 

Many thanks in advance!

 

D

 

Comments

  • GrantSmith
    GrantSmith Indiana 🔴

    Hi @DannyLewis 

     

    If the format is the same with LAST NAME, FIRST NAME then yes, you can utilize a beast mode to reorganize your names.

     

    Here's some examples using TRIM (trim / remove spaces from the start and beginning of a string), INSTR (find the location of a string inside another string) and SUBSTR (take a part of a string) functions.

     

    First Name

    TRIM(SUBSTRING(`Name`, INSTR(`Name`, ',') + 1, LENGTH(`Name`) - INSTR(`Name`, ',')))  -- + 1 to ignore comma

     

    Last Name

    TRIM(SUBSTRING(`Name`, 1, INSTR(`Name`, ',') - 1))  -- -1 to ignore the comma

     

     

    Combining them both together using CONCAT

    CONCAT(
  TRIM(SUBSTRING(`Name`, INSTR(`Name`, ',') + 1, LENGTH(`Name`) - INSTR(`Name`, ',')))  -- + 1 to ignore comma
  , ' '
  , TRIM(SUBSTRING(`Name`, 1, INSTR(`Name`, ',') - 1))  -- -1 to ignore the comma
)

     

Categories