Finding keywords in text and linking to products

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Finding keywords in text and linking to products

Author

Topic

mike13
Posting Yak Master

219 Posts

Posted - 2010-12-06 : 03:56:57

Hi All,

I got a piece of text and want to create links to my products based on keyword/product that are in the text.

My idea was:
- Split the text in an array
- Then with each array item search in product names
- retrieve and build the link around it

But i got no idea how to do this in SQL

Any idea would be very welcome

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-12-06 : 04:04:30

Please show some sample data ?

Vaibhav T

If I cant go back, I want to go fast...

mike13
Posting Yak Master

219 Posts

Posted - 2010-12-06 : 04:19:57

Hi,

text ex: I love the color of the grass in this park

product table:

product name: Fast growing grass
Productid: 456

product url view:
productid,productURL

So the text(array) does a search in product names, and then with product id, get the url from view with product urls.
then builds the html around the word

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-12-06 : 06:08:40

Try this -


Declare @Text Varchar(100),@delimiter NVARCHAR(5)
Set @Text = 'I love the color of the grass in this park'
set @delimiter = ' '
Declare @textXml Xml
Select @TextXml = Cast('<d>' + Replace(@Text, @Delimiter,'</d><d>') + '</d>' As Xml );

SELECT DISTINCT ProductName, ProductURL FROM Product P 
INNER JOIN ProductURL PU ON P.ProductID = PU.ProductID
CROSS APPLY 
(
SELECT T.split.value('.', 'nvarchar(max)') AS data
FROM @textXML.nodes('/d') T (split)
) A WHERE ProductName LIKE '%'+data+'%'

Vaibhav T

If I cant go back, I want to go fast...

Kristen
Test

22859 Posts

Posted - 2010-12-06 : 09:06:47

"I love the color of the grass in this park"

What if I search on "loves grass" or "like grass"?

We have a Synonyms table on our keyword system that takes care of that, but you might find a free-text search tool easier / less effort to set up. SQL has "full text search" that may do what you need.

mike13
Posting Yak Master

219 Posts

Posted - 2010-12-09 : 18:01:14

Hi Kristen,

Any sample of using that since i never used it

tnx

Kristen
Test

22859 Posts

Posted - 2010-12-10 : 02:31:15

http://msdn.microsoft.com/en-us/library/ms142571.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources