Return every instance of a string contains search
Use following query if you want to count the number of occurrence of the string 'first' in the TextColumn each row.

SELECT (LEN(TextColumn) - 
LEN(REPLACE(TextColumn, 'first', '')))/LEN('first') as NumOfOccurrence, 
TextColumn FROM dbo.SampleTable;


Update: To cover the trailing space case (as caught by Twinkles in the following comment), I modified the query as below. 

SELECT (LEN(TextColumn) - 
LEN(REPLACE(TextColumn, 'a ', '')))/(LEN('a ' + 'x') - 1) as NumOfOccurrence, 
TextColumn, id FROM dbo.SampleTable;


Following code gets the multiple instances in a New String Column, with separator defined as @sep and search word defined as @word, where @sep &lt;&gt; @ word:

declare @sep as char(2);
declare @word as varchar(24);
set @sep = ', ';
set @word = 'first';

SELECT  ID, TextColumn, ISNULL(STUFF(REPLICATE(@sep + @word, (LEN(TextColumn) - 
LEN(REPLACE(TextColumn, @word, '')))/(LEN(@word + 'x') - 1)), 1, 1, ''), '') 
as [New String Column] FROM dbo.SampleTable;