Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have this sample table and data

CREATE TABLE [dbo].[SampleTable](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [TextColumn] [nvarchar](1000) NOT NULL,
    [Id] ASC

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.  ')

With The Below Query

;with cte as (SELECT  
      a.Id ,
      CASE When LEN(TextColumn) <= 60
          Then TextColumn 
          ELSE LEFT(TextColumn, 60) + '...'
          END As Target,
          charindex('first', TextColumn) as SearchPos
       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.

share|improve this question
up vote 2 down vote accepted

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;
share|improve this answer
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. – Twinkles Apr 7 '14 at 13:37
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. – Binaya Regmi Apr 7 '14 at 14:06

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.