Compare the text of two columns

1

Hi to everyone!

I’m working on this dataset to cleaning it
https://www.kaggle.com/heesoo37/120-years-of-olympic-history-athletes-and-results

Now I’m trying to delete from the column Event the words that are contained in the column Sport. (I’ve already done some modification indeed in the column Event I’ve deleted the occurance of the words “man’s” and “women’s”).

Attached you’ll find

The current situtation and the desired result:

Attachment
Attachment.PNG644×1167 74 KB

How can I solve the problem?

I hope I have been clear, Thank you in advance for help.:blush:

2

Hey @Sboorn,

To remove the duplicated sport prefix from the event field, select the drop down arrow on the Event column, choose calculated field, and paste the below SQL snippet in the text box. Leaving the other two fields as their defaults will replace the existing Event field with this new definition, effectively deleting the extra Sport value from the Event field.

SUBSTR(Event, STRPOS(Sport, Event) + LENGTH(Sport) + 2)
2 Likes