This is one way of parsing out the tweets... it's not perfect, but it's closer than you are now.

DECLARE @TextString VARCHAR(1000) = 'got me a text file with tweets in it like this one http://t.co/R3ukfX6abd and then more text and then another one http://t.co/R3ukfX6abb and then some more text.';

SELECT *

FROM (

SELECT ds8k.Item

FROM SCRIDB.dbo.DelimitedSplit8K(@TextString,' ') ds8k ) x

WHERE x.Item LIKE 'http://%';

You need the code for DelimitedSplit8K to do this, which is here[/url].