in semantic database want to get rank of every word in conatins table

Question

0.00/5 (No votes)

See more:

In semanctic database:

SQL

set @str='java and sql and unix devloper or php and fox devloper'

SELECT K.RANK, AddressLine1, City
FROM Person.Address AS A
  INNER JOIN
  CONTAINSTABLE(Person.Address, AddressLine1,@str) AS K
  ON A.AddressID = K.[KEY]

above code fetches the rank of complete string when string str is passed to function CONTAINSTABLE.
but I need it separates all the words and fetches the rank of all the words. Separator should be 'AND' or 'OR'.
Output should be in format of commseperated rank with every word like this

word             rank
java              23
sql               21
unix devloper     20
php               30
fox devloper      23

I want to get rank of every word. Please help me.

Posted 6-Jan-16 6:24am

payaljain123

Updated 6-Jan-16 8:33am

Maciej Los

v2

Add a Solution

Comments

Maciej Los 6-Jan-16 15:42pm

I don't believe it's possible.
PLease, see: Limit Search Results with RANK especially How Search Query Results Are Ranked section.

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)

Maciej Los · Accepted Answer · 2016-01-06T12:47:00

I'm almost sure it's impossible to achieve by using CONTAINSTABLE statement. The only way to achieve that is to write custom function which will get comma-separated list of words and return the sum of occurrences of each word in one record.

I found this: c# - SQL Server best method to match word phrases and order relevence - Stack Overflow[^]. An author of most valuable answer (4 upvotes) shows a way how to get the number of occurrences of each word from sentence in a specific column of searched table.

This might be helpful too:
Quantifying Text differences in TSQL[^]
Pattern Matching: the Gestalt Approach | Dr Dobb's[^]
Limit Search Results with RANK[^] - How Search Query Results Are Ranked section.