way to split column name into first and last



Is there a way to split a field if I have into 2? I have a field name, for example Bob Smith, can I split this into 2 so having 'Bob' and 'Smith' for first name and last name

Best Answers

  • Valiant
    Answer ✓

    Here you go:


    First Name:

    LEFT(`name`, (INSTR(`name`, ' ')-1))

    Last Name:

    RIGHT(`name`, LENGTH(`name`)-INSTR(`name`, ' '))

    Hope that helps,


    **Please mark "Accept as Solution" if this post solves your problem
    **Say "Thanks" by clicking the "heart" in the post that helped you.

  • Hi,


    In Magic ETL, in the Edit Columns group of actions, you'll find 'Split Column' you can use this action to take a full name, and split it into a first and last name.  (See image)


    Hope that helps!


    Name Split.jpg


  • that worked, thank you

  • yes , it worked

  • Hi Nick, I'm trying to split my column similarly however the data in the original column is formatted as, "Stretch Wrap & Strapping - Copy". I want to split this column such that the "Stretch Wrap & Strapping" is in one column and the other column reads, "Copy". I tried making my custom deliminator, " - " however the split column isnt working... any ideas on how to trouble shoot? Thanks!