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 Windows 2000 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;

This page was last updated on May 01, 2006 04:28 PM.