Extract multiple string parts from a string

[eo]

Using a Twitter Tweet loaded to a SQL table as an example, how would I create new columns to show # and @ string parts. For example the column called 'Tweet' contains 'Our home @pmanfoggers #home #house #garden #adelaide #southaustralia #building #brick #bricklove...

http://t.co/YkaHqYg6

<via #instagram>'

I would like to create 6 additional VIEW columns that shows the 1st 3 # (hashtags) and the 1st 3 @ references.

The result should look like this:

Tweet - Our home @pmanfoggers #home #house #garden #adelaide #southaustralia #building #brick #bricklove...

http://t.co/YkaHqYg6

<via #instagram>
Hashtag1 - #home
Hashtag2 - #house
Hashtag3 - #garden
Ref1 - @pmanfoggers
Ref2 - NULL
Ref3 - NULL

Can anyone help with a potential query?



EO
Hertfordshire, England

 

[drlex]

In your position, I'd use a split function (e.g.

http://www.kodyaz.com/articles/t-sql-convert-split-delimeted-string-as-rows-using-xml.aspx)

on the text to get the words (with row numbers to show the order), and then filter the output set for the required # & @ prefixed words.

soi là, soi carré