Create a migration script to split the name field into firstname and lastname.

[John-Paul-Larkin]

Current behaviour:
Our user table currently has three “name” fields. The name field is populated by the user's name sourced from their GitHub profile.

New Onboarding Process:
We are introducing a new onboarding form to collect user metrics, which includes separate inputs for firstName and surName.

Migration Task:
We need to create an SQL/Node.js script to parse the existing name field into firstName and surName. These fields will be used in the onboarding form. If there are any issues with the migration (e.g., parsing errors or incorrect data), users will have the option to correct their names during the onboarding process.

ChatGPT kindly suggested these possible migration issues:

Multiple first names: Example: John Paul Larkin – "Larkin" could be incorrectly parsed as a last name.
Single word names: Example: Madonna – No last name available for splitting.
Prefixes/suffixes: Example: Dr. John Smith Jr. – Prefixes or suffixes may be included in the wrong field.
Hyphenated names: Example: Anna-Marie Johnson – Hyphenated names might split incorrectly.
Non-Western names: Example: Wang Xiaoming – Family names might come first.
Apostrophes or special characters: Example: O'Connor – Special characters could cause errors.
Formatting issues: Example: John Doe or JaneDoe – Extra spaces or no spaces at all.
Empty names: Users may not have a name set in their GitHub profile.

Connected issues
#1003
#1004
#1005
#1006
#1008
#1009
#1011

[JohnAllenTech]

Can you put this one in my name please.

I have it done in a node script but obviously all the ChatGPT edge cases are coming up.

  UPDATE "user"
    SET "firstName" = CASE
              WHEN name = '' THEN ''
              ELSE SPLIT_PART(name, ' ', 1)
              END,
        surname = CASE
              WHEN name = '' THEN ''
              ELSE SUBSTRING(name FROM POSITION(' ' IN name) + 1)
              END;
    `;

This will always add the first string before a space as the firstName and the remaining string as the surname.

So John Allen => John + Allen
John William Allen => John + William Allen

Ill see how many of the edge cases I can account for with enhancements to the above

Currently I have it running from a node script similar to the nuke script but I am trying to run it as a drizzle migration. What is the preferred way to do this?

[John-Paul-Larkin]

Thanks John 🥳
The odd edge case is not the end of the world. Users will be redirected to a new form once this issue is complete anyway.

[NiallJoeMaher]

Tagged you in the chat. I think I'm going to change the issues to keep the full name after realising some of our asian users might have difficulty with the new change.

[JohnAllenTech]

Holding off on this until @NiallJoeMaher decides how he wants to go forward