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%'
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
drowning
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)
AS
BEGIN
DECLARE @returnVal varchar(100)
DECLARE @offset int
set @returnVal = ''
set @offset = @width / 2
declare @pos int
set @pos = charindex(@find, @source)
while @pos > 0
begin
set @returnVal = @returnVal + '...' +
substring(@source,
@pos - @offset,
len(@find) + 2*@offset)
set @pos = charindex(@find, @source, @pos+len(@find))
end
RETURN @returnVal
END
Some noddy data to play with...
create table scratch
(
searchField varchar(50)
)
go
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')