<p>I have lots of article store in MS SQL server 2005 database in a table called Articles-</p>

<pre><code>"Articles (ArticleID, ArticleTitle, ArticleContent)"
</code></pre>

<p>Now I want some SP or SQL query which could return me similar Article against any user's input (very much like "Similar Posts" in blogs OR "Related Questions" in stackoverflow). The matching should work on both ArticleTitle and ArticleContent. The query should be intelligent enough to sort the result on the basis on their relevancy.</p>

<p>Is it possible to do this in MS SQL Server 2005?</p>

[{'answer_id': 39257, 'author': 'aku', 'author_id': 1196, 'author_profile': 'https://Stackoverflow.com/users/1196', 'pm_score': 0, 'selected': False, 'text': '<p>First of all you need to define what article similarity means.<br>\nFor example you can associate some meta information with articles, like tags.<br>\nTo be able to find similar articles you need to extract some features from them, for example you can build full text index.</p>\n\n<p>You can take advantage of full text search capability of MSSQL 2005</p>\n\n<pre><code>-- Assuming @Title contains title of current articles you can find related articles runnig this query  \nSELECT * FROM Acticles WHERE CONTAINS(ArticleTitle, @Title)\n</code></pre>\n'}, {'answer_id': 39270, 'author': 'Ivan Bosnic', 'author_id': 3221, 'author_profile': 'https://Stackoverflow.com/users/3221', 'pm_score': 0, 'selected': False, 'text': "<p>I think the question is what 'similar' means to you. If you create a field for user to input some kind of tags, it becomes much more easier to query.</p>\n"}, {'answer_id': 39316, 'author': 'GateKiller', 'author_id': 383, 'author_profile': 'https://Stackoverflow.com/users/383', 'pm_score': 2, 'selected': True, 'text': "<p>Something like this might work, a kind of ranking system. You would probably have to split the string in your application to build a SQL string, but I have used similar to build an effective site search.</p>\n\n<pre><code>Select\nTop 10\nArticleID,\nArticleTitle,\nArticleContent\nFrom\nArticles\nOrder By\n(Case When ArticleTitle = 'Article Title' Then 1 Else 0 End) Desc,\n(Case When ArticleTitle = 'Article' Then 1 Else 0 End) Desc,\n(Case When ArticleTitle = 'Title' Then 1 Else 0 End) Desc,\n(Case When Soundex('Article Title') = Soundex(ArticleTitle) Then 1 Else 0 End) Desc,\n(Case When Soundex('Article') = Soundex(ArticleTitle) Then 1 Else 0 End) Desc,\n(Case When Soundex('Title') = Soundex(ArticleTitle) Then 1 Else 0 End) Desc,\n(Case When PatIndex('%Article%Title%', ArticleTitle) &gt; 0 Then 1 Else 0 End) Desc,\n(Case When PatIndex('%Article%', ArticleTitle) &gt; 0 Then 1 Else 0 End) Desc,\n(Case When PatIndex('%Title%', ArticleTitle) &gt; 0 Then 1 Else 0 End) Desc,\n(Case When PatIndex('%Article%Title%', ArticleContent) &gt; 0 Then 1 Else 0 End) Desc,\n(Case When PatIndex('%Article%', ArticleContent) &gt; 0 Then 1 Else 0 End) Desc,\n(Case When PatIndex('%Title%', ArticleContent) &gt; 0 Then 1 Else 0 End) Desc\n</code></pre>\n\n<p>You can then add/remove case statements from the order by clause to improve the list based on your data.</p>\n"}]
['https://Stackoverflow.com/questions/39240', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/191/']