Show Matches in sql search results

Question

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|

Posted 31-Oct-13 16:27pm

Mr.TMG

Add a Solution

Comments

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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

cigwork · Answer 1 · 2013-11-01T10:57:00

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.

SQL

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

SQL

er sling swivel
drowning
 failing hands

Function usage would be something like this...

SQL

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.

SQL

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

SQL

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

  -- Using charindex you don't need the wild card characters.
  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...

SQL

create table scratch
(
  searchField varchar(50)
)
go

SQL

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