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

DannyLewis

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

 

GrantSmith

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
)