Click here to Skip to main content
15,671,471 members
Please Sign up or sign in to vote.
5.00/5 (1 vote)
See more:
Hi all,
I would like to display a snippet of text for my end user, to show them where their search criteria matches in the SQL entry.

If my user provides 'the' as search criteria, then I would like to return 'the' along whith a group of the surrounding characters(20 or so). Id like to collect this group of characters for each instance of the search criteria found and return it as CSV with a provided Comma separator.

Say I have a table [MyTable] with a column [TextColumn (Nvarchar)]

We will use one entry where the TextColumn contains the fallowing data.

' You gonna get used to wearing them chains after a while, Luke. Don't you never stop listening to them clinking, 'cause they gonna remind you what I been saying for your own good.'

Would return to me as:

'used to wearing them chains after a while|
stop listening to them clinking|
cause they gonna remind you what|

('|' is my comma separator in this example)

Can anyone advise me on how to accomplish this? I've never gone this deep into MSSQL.

And on this topic... would it be better for the user to just return the entire sentence?
You gonna get used to wearing them chains after a while, Luke|
Don't you never stop listening to them clinking, 'cause they gonna remind you what I been saying for your own good|
RedDk 1-Nov-13 12:51pm    
Use SQL Server and a SELECT query in TSQL.
Mr.TMG 1-Nov-13 13:20pm    
I'm not asking the question correctly, gotcha... I'll try to write it a little better.

1 solution

If you just want to find the first occurrence then patindex() will work. If you want to find all instances and display them then the simplest solution is likeliest to be a user defined function, which can wrap a loop, see below. You may need to tidy up end of string handling in both of the outlines below, but they should get you started.

Going with the simplest idea first.
declare @offset int
set @offset = 5

declare @find varchar(10)
set @find = '%ing%'

-- patindex finds the first instance of a pattern in a string
-- we use @offset to set the width of the fragment returned
select substring(searchField, 
                 patindex(@find, searchField) - @offset,
                 len(@find) + 2*@offset) as found
from   scratch 
where  searchField like @find

gets the following from the sample data shown below

er sling swivel
 failing hands 

Function usage would be something like this...
select dbo.LocatePattern(searchField, 'th', 10)
from   scratch 
where  searchField like '%th%'

..and will return the following from the sample data at the end of this lot.

...toppeth one ...e in three
...rode the six
... but this fl
...With a ba...t in the mid

One possible way of coding up a locate function.

CREATE FUNCTION LocatePattern(@source varchar(100), @find varchar(50), @width int)
returns varchar(100)
  DECLARE @returnVal varchar(100)
  DECLARE @offset int 
  set @returnVal = ''
  set @offset = @width / 2

  -- Using charindex you don't need the wild card characters.
  declare @pos int
  set @pos = charindex(@find, @source)
  while @pos > 0
    set @returnVal = @returnVal + '...' +
                               @pos - @offset,
                               len(@find) + 2*@offset)
    set @pos = charindex(@find, @source, @pos+len(@find))                

 RETURN @returnVal


Some noddy data to play with...

create table scratch
  searchField varchar(50)

insert into scratch(searchfield) values ('he stoppeth one in three')
insert into scratch(searchfield) values ('I wandered lonely as a cloud')
insert into scratch(searchfield) values ('it deepens like a coastal shelf')
insert into scratch(searchfield) values ('rode the six hundred')
insert into scratch(searchfield) values ('for she invented medicinal compound')
insert into scratch(searchfield) values ('mark but this flea')
insert into scratch(searchfield) values ('come friendly bombs and fall')
insert into scratch(searchfield) values ('With a bald spot in the middle of my')
insert into scratch(searchfield) values ('the upper sling swivel, whose use you will see')
insert into scratch(searchfield) values ('And for that minute a blackbird sang')
insert into scratch(searchfield) values ('As under a green sea, I saw him drowning')
insert into scratch(searchfield) values ('To you from failing hands we throw')
insert into scratch(searchfield) values ('These hedge-rows, hardly hedge-rows, little lines')
insert into scratch(searchfield) values ('Plunged in the battery-smoke')
insert into scratch(searchfield) values ('Of blinding windscreens, smelt the fish-dock; thence ')
insert into scratch(searchfield) values ('Lest he owre proud and high should turn')
Share this answer

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900