MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table test(memberid   int,content varchar(200));

insert into test values (1,'hello @abc');
insert into test values (2,'Im happy when @sunny @rainy ');
with recursive cte(memberid,c1,n,cnt) As (

select memberid,SUBSTRING(content FROM (LOCATE('@',content))+1) as c1,1 as n,length(content)-length(replace(content ,'@','')) as cnt FROM test
union all
select memberid,SUBSTRING(c1 FROM (LOCATE('@',c1))+1),n+1,cnt from cte
where n<cnt
)
select distinct memberid,SUBSTRING_INDEX(c1, ' ', 1) as text_after from cte


memberid
text_after


1
abc

2
sunny

2
rainy


/*Added few more sample just for testing*/
insert into test values (1,'hello @abc hope @efg is doing good');
insert into test values (2,'Im happy when @sunny but not when @rainy ');
with recursive cte(memberid,c1,n,cnt) As (

select memberid,SUBSTRING(content FROM (LOCATE('@',content))+1) as c1,1 as n,length(content)-length(replace(content ,'@','')) as cnt FROM test
union all
select memberid,SUBSTRING(c1 FROM (LOCATE('@',c1))+1),n+1,cnt from cte
where n<cnt
)
select distinct memberid,SUBSTRING_INDEX(c1, ' ', 1) as text_after from cte order by memberid


memberid
text_after


1
abc

1
efg

2
rainy

2
sunny