Custom Noise Word and Thesaurus UDF

Question

Post reply

Custom Noise Word and Thesaurus UDF

Dooza-770962

Ten Centuries

Points: 1021
More actions
July 25, 2008 at 9:20 am

#70477

Can anyone help me make a function that will take a string, remove noise words listed in a table, add matching words from a thesaurus table, and output the words separated by a space?
Cheers,
Steve

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

sql_er SSCarpal Tunnel Points: 4135 More actions · Answer 1

Have you tried anything yourself?

Have you progressed any?

Can you give us more details?

Ron McCullough SSC Guru Points: 63877 More actions · Answer 2

Please follow the link in the signature box and follow the instructions so as to present a question such that it will assist people who are more than willing to assist you

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Dooza-770962 Ten Centuries Points: 1021 More actions · Answer 3

sql_er (7/25/2008)
Have you tried anything yourself?
Have you progressed any?
Can you give us more details?

Sorry for the lack of detail, it was the end of a long frustrated week where I made little progress...its amazing what some sleep can do!

This morning I found this: http://www.dbforums.com/archive/index.php/t-329694.html

It showed an example of how to remove noise words from a string. I was able to create this:

CREATE function [dbo].[fn_thesaurus](@str varchar(200))

returns varchar(200) as begin

declare @txt varchar(200) declare

@t table(word varchar(50))

--Split search text into words - space separator

insert @t(word) select value from

dbo.fn_Split(@str,' ')

--insert thesaurus words

insert @t(word) select synonyms from tblThesaurus inner join @t on word = tblThesaurus.product_name

--remove original word that matached the thesaurus word

delete from @t from @t t join

tblThesaurus s (nolock) on t.word = s.product_name

--remove noise words by joining to noise table

delete from @t from @t t join

tblNoise s (nolock) on t.word=s.noiseword

--reassemble string

SELECT @txt = COALESCE(@txt +' ','') + word FROM @t SELECT @txt

=@txt

return @txt end

It takes the string, adds matching thesaurus words, removes the matched word, and removes any noise words.

My main search function will add the quotes and "AND" depending on the search being carried out. This was simpler than I imagined, but since I have never create a function before I thought it was going to be much harder.

Steve