how order sql result in order of more matches

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

General SQL Server Forums

New to SQL Server Programming

how order sql result in order of more matches

Author

Topic

bargan
Starting Member

4 Posts

Posted - 2015-04-16 : 10:49:50

sql server rank result in multiple words in query
I using classic ASP

I want that the result appears ranked when the client put three words in the search form EXAMPLE "white desk chair" first Result Containing the three words after two and after the one word

white desk chair (3 matches)
red desk chair (2 matches)
black desk (1 match)

thanks

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2015-04-16 : 11:06:41

Something like the following?

CREATE TABLE #SearchAgainst
(Search varchar(2000))

INSERT INTO #SearchAgainst
VALUES('white desk chair'),('red desk chair'),('black desk ')

DECLARE @SearchTerms TABLE (SearchTerm varchar(50))

INSERT INTO @SearchTerms
VALUES('white'),('desk'),('chair')

SELECT A.Search, SUM(CASE WHEN R > 0 THEN 1 ELSE 0 END) Results
FROM #SearchAgainst A
CROSS APPLY (SELECT PATINDEX('%' + ab.SearchTerm + '%',A.Search) R FROM @SearchTerms ab ) B
GROUP BY A.Search
ORDER BY 2 DESC

bargan
Starting Member

4 Posts

Posted - 2015-04-16 : 21:46:58

Field "searchFor" search in:

articleDescription
articleDescriptionSecundary
articleInternalCode
articleBarCode

CODE :
this code show results for Id order but i Need for more matches

<%
a=Split (Request("searchFor"))
for each x in a

strSQL = "SELECT * FROM article WHERE articleDescription COLLATE SQL_LATIN1_GENERAL_CP1_CI_AI LIKE '%" & x & "%' AND articleActive = 1 OR articleDescriptionSecundary COLLATE SQL_LATIN1_GENERAL_CP1_CI_AI LIKE '%" & x & "%' AND articleActive = 1 OR articleInternalCode LIKE '%" & x & "%' AND articleActive = 1 OR articleBarCode LIKE '%" & x & "%' AND articleActive = 1 "
Set rs = Server.CreateObject("ADODB.recordset")
next
%>

I want that the result appears ranked when the client put three words in the search form EXAMPLE "white desk chair" first Result Containing the three words after two and after the one word

white desk chair (3 matches)
red desk chair (2 matches)
black desk (1 match)

thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources