Sql-server – Return every instance of a string contains search

sql serversql-server-2012t-sql

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.

Best Answer

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;
Related Question