Binaryworld - Custom Ranking for Search conditions ( Similar to Full-Text Rank colum ... [ SQL Server -> FullText ]

This example demonstrates how to generate some ranking column for string searches. This column can be something like the RANK column generated by the SQL70 Full-Text feature.

Click here to copy the following block

DECLARE @Str varchar( 30 ) , @SearchStr varchar( 30 )
SELECT @Str = 'SQL Server 6.5 , SQL Server 7.0 , SQL Server 2000' ,
    @SearchStr = 'SQL'
-- This counts the number of occurrences of the search string &
-- this value can be used as ranking
SELECT LEN( @Str ) - LEN( REPLACE( @Str , @SearchStr , '' ) ) AS Ranking
GO
-- This solution can be extended further by using a
-- a #KeyWords table with user-defined weights
CREATE TABLE #KeyWords (
KeyWord varchar( 30 ) ,
Weight decimal( 3 , 2 )
);

INSERT INTO #KeyWords VALUES( 'MS SQL' , 9.99 );
INSERT INTO #KeyWords VALUES( 'Oracle' , 8.99 );
INSERT INTO #KeyWords VALUES( 'DB2' , 8.89 );
INSERT INTO #KeyWords VALUES( 'FOX' , 7.77 );
INSERT INTO #KeyWords VALUES( 'SQL-92' , 6.66 );

CREATE TABLE #SearchTbl (
TextID int IDENTITY ,
Descr varchar( 255 )
);

INSERT INTO #SearchTbl VALUES( 'This is MS SQL Server 6.5 , MS SQL Server 7.0 , MS SQL Server 2000 newsgroup.' );
INSERT INTO #SearchTbl VALUES( 'ANSI SQL-92 is quite cool.' );
INSERT INTO #SearchTbl VALUES( 'Oracle 8i has native java support.' );
INSERT INTO #SearchTbl VALUES( 'DB2 has the top TPC-C benchmark. And DB2 was running on Win2k Advanced Server.' );
INSERT INTO #SearchTbl VALUES( 'Visual FoxPro is an awesome desktop database with powerful GUI features.' );
GO

SELECT k.Weight * ( LEN( s.Descr ) -
          LEN( REPLACE( s.Descr , k.KeyWord , '' ) ) ) AS Ranking ,
    s.TextID , s.Descr
FROM #SearchTbl AS s
JOIN #KeyWords AS k
ON Descr LIKE '%' + k.KeyWord + '%'
ORDER BY Ranking DESC;

/*
Ranking TextID Descr
------- ------ --------------------------------------------------------------------------------------
179.82   1 This is MS SQL Server 6.5 , MS SQL Server 7.0 , MS SQL Server 2000 newsgroup.
53.94   3 Oracle 8i has native java support.
53.34   4 DB2 has the top TPC-C benchmark. And DB2 was running on Windows 2000 Advanced Server.
39.96   2 ANSI SQL-92 is quite cool.
23.31   5 Visual FoxPro is an awesome desktop database with powerful GUI features.

*/
GO

DROP TABLE #SearchTbl;
DROP TABLE #KeyWords;