Return every instance of a string contains search

Question

I have this sample table and data

CREATE TABLE [dbo].[SampleTable](
 [Id] [int] IDENTITY(1,1) NOT NULL,
 [TextColumn] [nvarchar](1000) NOT NULL,
 CONSTRAINT [PK_SampleTable] PRIMARY KEY CLUSTERED 
(
 [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[SampleTable] ON 
INSERT [dbo].[SampleTable] ([Id], [TextColumn]) VALUES (1, N'This is the first Test string')
INSERT [dbo].[SampleTable] ([Id], [TextColumn]) VALUES (2, N'This is the second one')
INSERT [dbo].[SampleTable] ([Id], [TextColumn]) VALUES (3, N'This is the first really long string of text that should be included in the result set.')
INSERT [dbo].[SampleTable] ([Id], [TextColumn]) VALUES (4, N'This is the second long string that will not be returned in the result set')
INSERT [dbo].[SampleTable] ([Id], [TextColumn]) VALUES (5, N'This is a really really long result set that should also be first included in the result set. It has a seperate sentence in it as well.')
INSERT [dbo].[SampleTable] ([Id], [TextColumn]) VALUES (6, N'Now this is a really really first one. It is so long that I have forgotten how long it really was. Well it could be really long but first lets do this. ')
SET IDENTITY_INSERT [dbo].[SampleTable] OFF

With The Below Query

;with cte as (SELECT 
 a.Id ,
 a.TextColumn, 
 CASE When LEN(TextColumn) <= 60
 Then TextColumn 
 ELSE LEFT(TextColumn, 60) + '...'
 END As Target,
 charindex('first', TextColumn) as SearchPos
 FROM
 SampleTable a 
 WHERE Contains((TextColumn), '%first%'))
select *, case when SearchPos > 20 then substring(TextColumn, SearchPos - 20, 20) + 'first' + 
substring(TextColumn, SearchPos + len('first'), 20) else substring(TextColumn, 1, SearchPos-1) + 'first' + substring(TextColumn, SearchPos + len('first'), 20)
 end as NewString
from cte 

I am trying to return every instance of a word with a Contains search. But the string could be in the middle of a large sting. So I am returning the string and a given length to the left and right of the searched string. Sort of like a summary. If the word occurs multiple times it is only returning the first instance and moving on to the next instance. How can I get it to return all instances even multiple ones in the same cell. So the item with Id of 6 should return twice not once because I am looking for the string 'first'. I am using Sql Server 2012. The New String Column Is What I am shooting for.

Answer 1

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 <> @ 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;

• Twinkles: LEN() returns the number of characters of the specified string expression, excluding trailing blanks. Your query returns wrong results if somebody for instance wants to exclude substrings inside of words by appending a blank.
• user353gre3: Good catch - Thanks Twinkles. Using (LEN(@SearchText + 'x') - 1) as divisor in the above query should solve it. I have updated the query to reflect the change.