Using CATX to extract text after a specific index word

sufiya — Mon, 07 Feb 2022 15:55:27 GMT

Hi, I have a column from which I need to extract text after a specific index word (MP).using the following code:

catx(" ", scan(TEST_NAME , 2, "MP")) as NAME,

this is what I end up with:

TEST_NAME	NAME
MP KONSTANTINE ABCDE
WNP BM OPS TEST MP CIGI FANNING	BM O
MP DAN BkENTHER
ERA MONTREAL TEST MP MARIA PUBRANTI	MARIA PUBRANTI
MP MARIA PUBRANTI
MP BRUCE WAYNE
MYABCD DIG CHAN TEST MP ABCDE DADOUN	ABCDE DADOUN
MP JENNIFER NOSNIKOF	OSNIKOF
MP STEVE PETERS	E PETERS

below is what I want - However, I can't seem to get the code right to extract the text after "MP", any suggestions, please? Kind regards 🙂

NAME

KONSTANTINE ABCDE

CIGI FANNING

DAN BkENTHER

MARIA PUBRANTI

BRUCE WAYNE

ABCDE DADOUN

MP JENNIFER NOSNIKOF

STEVE PETERS

Re: Using CATX to extract text after a specific index word

PaigeMiller — Mon, 07 Feb 2022 16:21:25 GMT

You can use FINDW and SUBSTR

newname=substr(test_name,findw(test_name,'MP')+3);

sufiya — Mon, 07 Feb 2022 17:21:16 GMT

Thank you this works!