This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
I am in the process of cleaning up customer data in our system. One of the issues I have run into is that at times the last name field contains the full customer name ('John Smith') and the first name field has the first name value as well ('John'). In reporting this will concatenate wrong ('John Smith, John').
I am looking for a method where I can use the value of first name and see if this is a substring of last name and then remove this substring in last name. So basically getting 'Smith' and 'John' in separate fields.
I have tried the find and replace tool. However this does not seem to work on record level, but rather over all records. All first name strings will be compared with all last names strings and if any match is found that substring will be removed. As a result records that had a last name 'John Smith' but were the first name was empty, are changed to 'Smith'. I would like the logic to work at record level only.
This seems to work for my problem. The test cases I have all turn out as they should. I will have our testers go through my output next week and see if they can still manage to find or create cases in which this logic does not work.