I have a SQL that I am using right now where I need to extract the words after @ in a column. They are twitter captions so there are multiple @'s tagged - This is the current code that I have
SELECT array_to_string(array_agg(word), ',') word_list FROM ( SELECT caption, split_part(split_part(t.value, '@',1), ' ',1) word FROM (select caption from "CONVIVA_ANALYTICS"."INSTAGRAM_POST_METRICS" where postid = '1971324349038518078_1547775030'), lateral flatten(split(caption, '@')) t WHERE t.value != '')
GROUP BY caption; The results from the query above are - have,revjahwar,nfl,nflnetwork
This is the column how it looks - have a shoe & sock line for u soon and great price points that fit your budget. #Truth @revjahwar #51&Done #21reasons #21dayswithPrime #IminmyPurpose #Purpose=Peace #iBelieve #Tiredofplayinggames #2019AintNobodyCare @nfl @nflnetwork
What I need help with is extracting those words without the first word of the column also coming out so I know I am pretty close but I cant figure out how to pull everything and not the first word in the column, any help is appreciated!