SQLTeam.com | Weblogs | Forums

Extracting words after @ from snowflake

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!

You might get a quicker answer from a snowflake forum?