Return group of words/characters around the found search term. 2

[iaresean]

Hi All;

I have written a basic search stored procedure to search through content areas of my website contained within columns of type 'text'.

What I am trying to do however is return to return a grouping of words around the matched text to display as a teaser on the frontend.

So if one searches for the word 'moon' and it is found within the text it would grab something like the closest 10 words on either side of the matched text like so:

"..I really hope this search thing works out for me. The moon is a really beautiful celestial body..."

I will seriously worship anyone who can tell me how I might do this! :-D

Sean.

 

[AlexCuse]

Are they really TEXT data type? You will have much better luck trying to search a varchar(8000) (for SQL 2000) or varchar(MAX) (for SQL 2005) column, as this gives you access to a whole different set of functions. So, if you can change the data type to varchar(x) that would be a good start.

With TEXT data type, I'm not sure how you could do it. Bu 8000 characters is a lot, I bet you can fit it in varchar.

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[iaresean]

Ok, I think I might be able to rustle up something.

For interests sake, how would I do it using a varchar data type? :-D

Sean.

 

[AlexCuse]

Ok, here is a way to do it with varchar. Because you want to do whole words, this is not as simple as you'd think :-(

I think the comments pretty well sum it up, if you need information about any of the string manipulation functions used please consult books online (SQL Server help)

Here goes

[COLOR=green]---set up for test search
[/color][COLOR=blue]declare[/color] @column [COLOR=blue]varchar[/color](1000), @search [COLOR=blue]varchar[/color](200)

[COLOR=blue]set[/color] @column = [COLOR=red]'The music companies, facing a long-term decline in sales, had resisted freeing up their digital songs from restrictions because they were afraid consumers would make numerous copies that would deprive the companies and their recording artists of revenue.'[/color]

[COLOR=blue]set[/color] @search = [COLOR=red]'they were afraid'[/color]


[COLOR=blue]select[/color] [COLOR=#FF00FF]charindex[/color]([COLOR=red]' '[/color], @column, 150)
[COLOR=green]---search for whole words starting from 50 characters back of search string start (start from next space)
[/color][COLOR=green]---this will count 150 characters ahead from start (+ however far it needs to go to find a word break)
[/color][COLOR=blue]select[/color] [COLOR=#FF00FF]substring[/color](
@column
, [COLOR=blue]case[/color] [COLOR=blue]when[/color] [COLOR=#FF00FF]charindex[/color](@search, @column) < 50
	[COLOR=blue]then[/color] 0
	[COLOR=blue]else[/color] [COLOR=#FF00FF]charindex[/color](@search, @column) - 50
		+ [COLOR=#FF00FF]charindex[/color]([COLOR=red]' '[/color], [COLOR=#FF00FF]substring[/color](@column, [COLOR=#FF00FF]charindex[/color](@search, @column) - 50, len(@column)))
	[COLOR=blue]end[/color]
, [COLOR=blue]case[/color] [COLOR=blue]when[/color] [COLOR=#FF00FF]charindex[/color](@search, @column) < 50
	[COLOR=blue]then[/color] [COLOR=#FF00FF]charindex[/color]([COLOR=red]' '[/color], @column, 150)
	[COLOR=blue]else[/color] 150 + [COLOR=#FF00FF]charindex[/color]([COLOR=red]' '[/color], [COLOR=#FF00FF]substring[/color](@column,
		[COLOR=#FF00FF]charindex[/color](@search, @column) - 50
		+ [COLOR=#FF00FF]charindex[/color]([COLOR=red]' '[/color], [COLOR=#FF00FF]substring[/color](@column, [COLOR=#FF00FF]charindex[/color](@search, @column) - 50, len(@column)))
		, len(@column)), 150)
	[COLOR=blue]end[/color])


[COLOR=green]--print the same thing for easy reading
[/color][COLOR=blue]print[/color] [COLOR=#FF00FF]substring[/color](
@column
, [COLOR=blue]case[/color] [COLOR=blue]when[/color] [COLOR=#FF00FF]charindex[/color](@search, @column) < 50
	[COLOR=blue]then[/color] 0
	[COLOR=blue]else[/color] [COLOR=#FF00FF]charindex[/color](@search, @column) - 50
		+ [COLOR=#FF00FF]charindex[/color]([COLOR=red]' '[/color], [COLOR=#FF00FF]substring[/color](@column, [COLOR=#FF00FF]charindex[/color](@search, @column) - 50, len(@column)))
	[COLOR=blue]end[/color]
, [COLOR=blue]case[/color] [COLOR=blue]when[/color] [COLOR=#FF00FF]charindex[/color](@search, @column) < 50
	[COLOR=blue]then[/color] [COLOR=#FF00FF]charindex[/color]([COLOR=red]' '[/color], @column, 150)
	[COLOR=blue]else[/color] [COLOR=#FF00FF]charindex[/color]([COLOR=red]' '[/color], [COLOR=#FF00FF]substring[/color](@column,
		[COLOR=#FF00FF]charindex[/color](@search, @column) - 50
		+ [COLOR=#FF00FF]charindex[/color]([COLOR=red]' '[/color], [COLOR=#FF00FF]substring[/color](@column, [COLOR=#FF00FF]charindex[/color](@search, @column) - 50, len(@column)))
		, len(@column)), 150)
	[COLOR=blue]end[/color])

I encourage you to play around with both the @column variable and the @search variable to see if it returns what you want in all cases. One thing I didn't do is write it to handle for columns <150 characters wide (or with < 150 characters from the start position), but you should be able to add a check for that pretty easily.

Hope it helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[AlexCuse]

Hey, I left this

select charindex(' ', @column, 150)

At the top for debugging purposes and forgot to delete it. You can disregard that line

Ignorance of certain subjects is a great part of wisdom

 

[iaresean]

Thanks alot! I will certainly put your logic to the test.

Yeah the string manipulation would be alot easier if SQL Server 2000 had built in Regular Expression support.

Thanks again;

Sean.

 

[ca8msm]

Although SQL Server 2000 doesn't have built in RegEx support, there is still a method that you can use to incorporate them. I believe it used vbscript or referenced a dll to actually perform the RegEx. I'm sure it was George or Denis (apologies if it wasn't!) who mentioned it so I'll see if I can dig out the thread that they referenced it in. If not, I'm sure someone will will be able to point out how to do it.


____________________________________________________________
Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]

Need help finding an answer? Try the Search Facility or read FAQ222-2244 on how to get better results.

 

[AlexCuse]

iaresean,

I considered trying the same thing that Mark is talking about last night for a web page that I am working on. However, I am not sure if the vbScript dll is registered on the server that holds my database (I doubt it is), and I don't know if it'd be worth the fee to have them register it for me.

I have an example of using regular expressions in t-SQL that I have actually started adapting for this very purpose. The example came courtesy of gmmastros. I imagine he will be along some time this morning, and be much better prepared to answer questions about it than I am.

Do you know whether or not vbScript DLL is registered on your server?

Ignorance of certain subjects is a great part of wisdom

 

[iaresean]

ca8msm -

Wow, seems like whereever I go a tip from you is sure to follow. :-D

Thanks for the advice, I will certainly do some reading up about this DLL.

----------

AlexCuse -

I look forward to hearing from gmmastros, if he is in the mood of sharing his wisdom.

Unfortunately this project I am going to be working on is going to run on another companies server. They are highly paranoid and inflexible so I doubt there is much chance I can get them to install the DLL. :-(

Although, it is a nice thing for me to keep in mind for future projects in which I have control over the DB server.

----------

Thanks for tips guys!

Sean.

 

[gmmastros]

Found it!

This is a function that can determine if an email address is valid. You will need to modify this to suit your needs. There are some security issue (particularly if you are using sql 2005). It may be worth a try, though.

[COLOR=blue]alter[/color] [COLOR=#FF00FF]Function[/color] IsValidEmail
  (@EmailAddress [COLOR=blue]VarChar[/color](100))
Returns [COLOR=blue]Bit[/color]
[COLOR=blue]As[/color]
  [COLOR=blue]Begin[/color]

    [COLOR=blue]Declare[/color] @RegExId [COLOR=blue]int[/color]
    [COLOR=blue]Declare[/color] @hr [COLOR=blue]int[/color]
    [COLOR=blue]Declare[/color] @Valid [COLOR=blue]Bit[/color]

    [COLOR=blue]EXEC[/color] @hr = sp_OACreate [COLOR=red]'VBScript.RegExp'[/color], @RegExId [COLOR=#FF00FF]OUT[/color]
    [COLOR=blue]EXEC[/color] @hr = sp_OASetProperty @RegExId, [COLOR=red]'Pattern'[/color], [COLOR=red]'^[a-zA-Z][\w\.-]*[a-zA-Z0-9]@[a-zA-Z0-9][\w\.-]*[a-zA-Z0-9]\.[a-zA-Z][a-zA-Z\.]*[a-zA-Z]$'[/color]
    [COLOR=blue]EXEC[/color] @hr = sp_OAMethod @RegExId, [COLOR=red]'Test'[/color], @Valid [COLOR=blue]OUTPUT[/color], @EmailAddress
    [COLOR=blue]EXEC[/color] @hr = sp_OADestroy @RegExId

    [COLOR=blue]Return[/color] @Valid
  [COLOR=blue]End[/color]

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[iaresean]

Awesome. :-D

Sean.