8

What is the best way to rank a sql varchar column by the number (count)/match of words in a parameter, with four distinct unique criteria. This is likely not a trivial question but I am challenged to order the rows based on "best match" using my criteria.

column: description varchar(100) Parameter: @MyParameter varchar(100)

Output with this order preference:

  • Exact match (entire string matches) - always first
  • Begins with (decending based on the parameter length of match)
  • Count of words rank with contiguous words ranking higher for the same count of words matched
  • Word(s) match anywhere (not contiguous)

Words might NOT match exactly, as partial matches of a word are allowed and likely, lessor value should be applied to partial words for ranking but not critical (pot would match each in: pot, potter, potholder, depot, depotting for instance). Begins with with other word matches should rank higher than those with no subsequent matches but that is not a deal killer/super important.

I would like to have a method to rank where the column "begins with" the value in the parameter. Say I have the following string:

'This is my value string as a test template to rank on.'

I would like to have, in the first case a rank of the column/row where the greatest count of words exist.

And the second to rank based on occurance (best match) at the begining as:

'This is my string as a test template to rank on.' - first
'This is my string as a test template to rank on even though not exact.'-second
'This is my string as a test template to rank' - third
'This is my string as a test template to' - next
'This is my string as a test template' - next etc.

Secondly:(possibly second set/group of data after the first (begins with) - this is desired

I want to rank (sort) the rows by the count of words in the @MyParameter that occur in @MyParameter with a rank where contiguous words rank higher than the same count separate.

Thus for the example string above, 'is my string as shown' would rank higher than 'is not my other string as' due the the "better match" of the contiguous string (words together) with the same count of words. Rows with a higher match (count of words that occur) would rank descending best match first.

If possible, I would like to do this in a single query.

No row should occur twice in the result.

For performance considerations, there will occur no more than 10,000 rows in the table.

The values in the table are fairly static with little change but not totally so.

I cannot change the structure at this time but would consider that later (like a word/phrase table)

To make this slightly more complicated, the word list is in two tables - but I could create a view for that, but one table results (smaller list) should occur prior to a second, larger data set results given the same match - there will be duplicates from these tables as well as within a table and I only want distinct values. Select DISTINCT is not easy as I want to return one column (sourceTable) which could quite possibly make the rows distinct and in that case only select from the first (smaller) table, but all the other columns DISTINCT is desired (do not consider that column in the "distinct" evaluation.

Psuedo Columns in table:

procedureCode   VARCHAR(50),
description VARCHAR(100), -- this is the sort/evaluation column
category    VARCHAR(50),
relvu       VARCHAR(50),
charge  VARCHAR(15),
active  bit
sourceTable   VARCHAR(50) - just shows which table it comes from of the two

NO unique index exists like an ID column

Matches NOT in a third table to be excluded SELECT * FROM (select * from tableone where procedureCode not in (select procedureCode from tablethree)) UNION ALL (select * from tabletwo where procedureCode not in (select procedureCode from tablethree))

EDIT: in an attempt to address this I have created a table value paramter like so:

0       Gastric Intubation & Aspiration/Lavage, Treatmen
1       Gastric%Intubation%Aspiration%Lavage%Treatmen
2       Gastric%Intubation%Aspiration%Lavage
3       Gastric%Intubation%Aspiration
4       Gastric%Intubation
5       Gastric
6       Intubation%Aspiration%Lavage%Treatmen
7       Intubation%Aspiration%Lavage
8       Intubation%Aspiration
9       Intubation
10      Aspiration%Lavage%Treatmen
11      Aspiration%Lavage
12      Aspiration
13      Lavage%Treatmen
14      Lavage
15      Treatmen

where the actual phrase is in row 0

Here is my current attempt at this:

CREATE PROCEDURE [GetProcedureByDescription]
(   
        @IncludeMaster  BIT,
        @ProcedureSearchPhrases CPTFavorite READONLY

)
AS

    DECLARE @myIncludeMaster    BIT;

    SET @myIncludeMaster = @IncludeMaster;

    CREATE TABLE #DistinctMatchingCpts
    (
    procedureCode   VARCHAR(50),
    description     VARCHAR(100),
    category        VARCHAR(50),
    rvu     VARCHAR(50),
    charge      VARCHAR(15),
    active      VARCHAR(15),
    sourceTable   VARCHAR(50),
    sequenceSet VARCHAR(2)
    )

    IF @myIncludeMaster = 0
        BEGIN -- Excluding master from search   
          INSERT INTO #DistinctMatchingCpts (sourceTable, procedureCode, description    ,   category  ,charge, active, rvu, sequenceSet
) 
      SELECT DISTINCT sourceTable, procedureCode, description, category ,charge, active, rvu, sequenceSet
          FROM (
                  SELECT TOP 1
                      LTRIM(RTRIM(CPT.[CODE])) AS procedureCode, 
                      LTRIM(RTRIM(CPT.[LEVEL])) AS description, 
                      LTRIM(RTRIM(CPT.[COMBO])) AS category,
                      LTRIM(RTRIM(CPT.[CHARGE])) AS charge,
                      ''True'' AS active,
                      LTRIM(RTRIM([RVU])) AS rvu,
                      ''0CPTMore'' AS sourceTable,
                      ''01'' AS sequenceSet
                  FROM 
                    @ProcedureSearchPhrases PP
                    INNER JOIN  [CPTMORE] AS CPT
                      ON CPT.[LEVEL] = PP.[LEVEL]
                  WHERE 
                      (CPT.[COMBO] IS NULL OR CPT.[COMBO] NOT IN (''Editor'',''MOD'',''CATEGORY'',''Types'',''Bundles''))
                      AND CPT.[CODE] IS NOT NULL
                      AND CPT.[CODE] NOT IN (''0'', '''')
                    AND CPT.[CODE] NOT IN (SELECT CPTE.[CODE] FROM CPT AS CPTE WHERE CPTE.[CODE] IS NOT NULL)
                  ORDER BY PP.CODE

          UNION ALL

                  SELECT 
                      LTRIM(RTRIM(CPT.[CODE])) AS procedureCode, 
                      LTRIM(RTRIM(CPT.[LEVEL])) AS description, 
                      LTRIM(RTRIM(CPT.[COMBO])) AS category,
                      LTRIM(RTRIM([CHARGE])) AS charge,
                      ''True'' AS active,
                      LTRIM(RTRIM([RVU])) AS rvu,
                      ''0CPTMore'' AS sourceTable, 
                      ''02'' AS sequenceSet
                  FROM 
                    @ProcedureSearchPhrases PP
                    INNER JOIN  [CPTMORE] AS CPT
                      ON CPT.[LEVEL] LIKE PP.[LEVEL] + ''%''
                  WHERE 
                      (CPT.[COMBO] IS NULL OR CPT.[COMBO] NOT IN (''Editor'',''MOD'',''CATEGORY'',''Types'',''Bundles''))
                      AND CPT.[CODE] IS NOT NULL
                      AND CPT.[CODE] NOT IN (''0'', '''')
                    AND CPT.[CODE] NOT IN (SELECT CPTE.[CODE] FROM CPT AS CPTE WHERE CPTE.[CODE] IS NOT NULL)

          UNION ALL

            SELECT 
                      LTRIM(RTRIM(CPT.[CODE])) AS procedureCode, 
                      LTRIM(RTRIM(CPT.[LEVEL])) AS description, 
                      LTRIM(RTRIM(CPT.[COMBO])) AS category,
                      LTRIM(RTRIM(CPT.[CHARGE])) AS charge,
                      ''True'' AS active,
                      LTRIM(RTRIM([RVU])) AS rvu,
                      ''0CPTMore'' AS sourceTable,
                      ''03'' AS sequenceSet
                  FROM 
                    @ProcedureSearchPhrases PP
                    INNER JOIN  [CPTMORE] AS CPT
                      ON CPT.[LEVEL] LIKE ''%'' + PP.[LEVEL] + ''%''
                  WHERE 
                      (CPT.[COMBO] IS NULL OR CPT.[COMBO] NOT IN (''Editor'',''MOD'',''CATEGORY'',''Types'',''Bundles''))
                      AND CPT.[CODE] IS NOT NULL
                      AND CPT.[CODE] NOT IN (''0'', '''')
                    AND CPT.[CODE] NOT IN (SELECT CPTE.[CODE] FROM CPT AS CPTE WHERE CPTE.[CODE] IS NOT NULL)

            ) AS CPTS
            ORDER BY 
                 procedureCode, sourceTable, [description]
        END -- Excluded master from search
    ELSE
        BEGIN -- Including master in search, but present favorites before master for each code
            -- Get matching procedures, ordered by code, source (favorites first), and description.
            -- There probably will be procedures with duplicated code+description, so we will filter
            -- duplicates shortly.
      INSERT INTO #DistinctMatchingCpts (sourceTable, procedureCode, description    ,   category  ,charge, active, rvu, sequenceSet) 
      SELECT DISTINCT sourceTable, procedureCode, description, category ,charge, active, rvu, sequenceSet
          FROM (
                  SELECT TOP 1
                      LTRIM(RTRIM(CPT.[CODE])) AS procedureCode, 
                      LTRIM(RTRIM(CPT.[LEVEL])) AS description, 
                      LTRIM(RTRIM(CPT.[COMBO])) AS category,
                      LTRIM(RTRIM(CPT.[CHARGE])) AS charge,
                      ''True'' AS active,
                      LTRIM(RTRIM([RVU])) AS rvu,
                      ''0CPTMore'' AS sourceTable,
                      ''00'' AS sequenceSet
                FROM 
                    @ProcedureSearchPhrases PP
                    INNER JOIN  [CPTMORE] AS CPT
                      ON CPT.[LEVEL] = PP.[LEVEL]
                  WHERE 
                      (CPT.[COMBO] IS NULL OR CPT.[COMBO] NOT IN (''Editor'',''MOD'',''CATEGORY'',''Types'',''Bundles''))
                      AND CPT.[CODE] IS NOT NULL
                      AND CPT.[CODE] NOT IN (''0'', '''')
                    AND CPT.[CODE] NOT IN (SELECT CPTE.[CODE] FROM CPT AS CPTE WHERE CPTE.[CODE] IS NOT NULL)
                  ORDER BY PP.CODE

                  UNION ALL

                  SELECT TOP 1
                      LTRIM(RTRIM(CPT.[CODE])) AS procedureCode, 
                      LTRIM(RTRIM(CPT.[LEVEL])) AS description, 
                      LTRIM(RTRIM(CPT.[CATEGORY])) AS category,
                      LTRIM(RTRIM(CPT.[CHARGE])) AS charge,
                      COALESCE(CASE [ACTIVE] WHEN 1 THEN ''True'' WHEN 0 THEN ''False'' WHEN '''' THEN ''False'' ELSE ''False'' END,''True'') AS active,
                      LTRIM(RTRIM([RVU])) AS rvu,
                      ''2MasterCPT'' AS sourceTable,
                      ''00'' AS sequenceSet
                  FROM 
                    @ProcedureSearchPhrases PP
                    INNER JOIN  [MASTERCPT] AS CPT
                      ON CPT.[LEVEL] = PP.[LEVEL]
                  WHERE 
                      CPT.[CODE] IS NOT NULL
                      AND CPT.[CODE] NOT IN (''0'', '''')
                    AND CPT.[CODE] NOT IN (SELECT CPTE.[CODE] FROM CPT AS CPTE WHERE CPTE.[CODE] IS NOT NULL)
                  ORDER BY PP.CODE

                  UNION ALL

                  SELECT 
                      LTRIM(RTRIM(CPT.[CODE])) AS procedureCode, 
                      LTRIM(RTRIM(CPT.[LEVEL])) AS description, 
                      LTRIM(RTRIM(CPT.[COMBO])) AS category,
                      LTRIM(RTRIM(CPT.[CHARGE])) AS charge,
                      ''True'' AS active,
                      LTRIM(RTRIM([RVU])) AS rvu,
                      ''0CPTMore'' AS sourceTable,
                      ''01'' AS sequenceSet
                FROM 
                    @ProcedureSearchPhrases PP
                    INNER JOIN  [CPTMORE] AS CPT
                      ON CPT.[LEVEL] = PP.[LEVEL]
                  WHERE 
                      (CPT.[COMBO] IS NULL OR CPT.[COMBO] NOT IN (''Editor'',''MOD'',''CATEGORY'',''Types'',''Bundles''))
                      AND CPT.[CODE] IS NOT NULL
                      AND CPT.[CODE] NOT IN (''0'', '''')
                    AND CPT.[CODE] NOT IN (SELECT CPTE.[CODE] FROM CPT AS CPTE WHERE CPTE.[CODE] IS NOT NULL)

                  UNION ALL

                  SELECT 
                      LTRIM(RTRIM(CPT.[CODE])) AS procedureCode, 
                      LTRIM(RTRIM(CPT.[LEVEL])) AS description, 
                      LTRIM(RTRIM(CPT.[CATEGORY])) AS category,
                      LTRIM(RTRIM(CPT.[CHARGE])) AS charge,
                      COALESCE(CASE [ACTIVE] WHEN 1 THEN ''True'' WHEN 0 THEN ''False'' WHEN '''' THEN ''False'' ELSE ''False'' END,''True'') AS active,
                      LTRIM(RTRIM([RVU])) AS rvu,
                      ''2MasterCPT'' AS sourceTable,
                      ''01'' AS sequenceSet
                  FROM 
                    @ProcedureSearchPhrases PP
                    INNER JOIN  [MASTERCPT] AS CPT
                      ON CPT.[LEVEL] = PP.[LEVEL]
                  WHERE 
                      CPT.[CODE] IS NOT NULL
                      AND CPT.[CODE] NOT IN (''0'', '''')
                    AND CPT.[CODE] NOT IN (SELECT CPTE.[CODE] FROM CPT AS CPTE WHERE CPTE.[CODE] IS NOT NULL)

                  UNION ALL

                  SELECT TOP 1
                      LTRIM(RTRIM(CPT.[CODE])) AS procedureCode, 
                      LTRIM(RTRIM(CPT.[LEVEL])) AS description, 
                      LTRIM(RTRIM(CPT.[COMBO])) AS category,
                      LTRIM(RTRIM(CPT.[CHARGE])) AS charge,
                      ''True'' AS active,
                      LTRIM(RTRIM([RVU])) AS rvu,
                      ''0CPTMore'' AS sourceTable,
                      ''02'' AS sequenceSet
                FROM 
                    @ProcedureSearchPhrases PP
                    INNER JOIN  [CPTMORE] AS CPT
                      ON CPT.[LEVEL] LIKE PP.[LEVEL] + ''%''
                  WHERE 
                      (CPT.[COMBO] IS NULL OR CPT.[COMBO] NOT IN (''Editor'',''MOD'',''CATEGORY'',''Types'',''Bundles''))
                      AND CPT.[CODE] IS NOT NULL
                      AND CPT.[CODE] NOT IN (''0'', '''')
                    AND CPT.[CODE] NOT IN (SELECT CPTE.[CODE] FROM CPT AS CPTE WHERE CPTE.[CODE] IS NOT NULL)
                  ORDER BY PP.CODE

                  UNION ALL

                  SELECT TOP 1
                      LTRIM(RTRIM(CPT.[CODE])) AS procedureCode, 
                      LTRIM(RTRIM(CPT.[LEVEL])) AS description, 
                      LTRIM(RTRIM(CPT.[CATEGORY])) AS category,
                      LTRIM(RTRIM(CPT.[CHARGE])) AS charge,
                      COALESCE(CASE [ACTIVE] WHEN 1 THEN ''True'' WHEN 0 THEN ''False'' WHEN '''' THEN ''False'' ELSE ''False'' END,''True'') AS active,
                      LTRIM(RTRIM([RVU])) AS rvu,
                      ''2MasterCPT'' AS sourceTable,
                      ''02'' AS sequenceSet
                  FROM 
                    @ProcedureSearchPhrases PP
                    INNER JOIN  [MASTERCPT] AS CPT
                      ON CPT.[LEVEL] LIKE PP.[LEVEL] + ''%''
                  WHERE 
                      CPT.[CODE] IS NOT NULL
                      AND CPT.[CODE] NOT IN (''0'', '''')
                    AND CPT.[CODE] NOT IN (SELECT CPTE.[CODE] FROM CPT AS CPTE WHERE CPTE.[CODE] IS NOT NULL)
                  ORDER BY PP.CODE

                  UNION ALL

                  SELECT 
                      LTRIM(RTRIM(CPT.[CODE])) AS procedureCode, 
                      LTRIM(RTRIM(CPT.[LEVEL])) AS description, 
                      LTRIM(RTRIM(CPT.[COMBO])) AS category,
                      LTRIM(RTRIM(CPT.[CHARGE])) AS charge,
                      ''True'' AS active,
                      LTRIM(RTRIM([RVU])) AS rvu,
                      ''0CPTMore'' AS sourceTable,
                      ''03'' AS sequenceSet
                FROM 
                    @ProcedureSearchPhrases PP
                    INNER JOIN  [CPTMORE] AS CPT
                      ON CPT.[LEVEL] LIKE PP.[LEVEL] + ''%''
                  WHERE 
                      (CPT.[COMBO] IS NULL OR CPT.[COMBO] NOT IN (''Editor'',''MOD'',''CATEGORY'',''Types'',''Bundles''))
                      AND CPT.[CODE] IS NOT NULL
                      AND CPT.[CODE] NOT IN (''0'', '''')
                    AND CPT.[CODE] NOT IN (SELECT CPTE.[CODE] FROM CPT AS CPTE WHERE CPTE.[CODE] IS NOT NULL)

                  UNION ALL

                  SELECT 
                      LTRIM(RTRIM(CPT.[CODE])) AS procedureCode, 
                      LTRIM(RTRIM(CPT.[LEVEL])) AS description, 
                      LTRIM(RTRIM(CPT.[CATEGORY])) AS category,
                      LTRIM(RTRIM(CPT.[CHARGE])) AS charge,
                      COALESCE(CASE [ACTIVE] WHEN 1 THEN ''True'' WHEN 0 THEN ''False'' WHEN '''' THEN ''False'' ELSE ''False'' END,''True'') AS active,
                      LTRIM(RTRIM([RVU])) AS rvu,
                      ''2MasterCPT'' AS sourceTable,
                      ''03'' AS sequenceSet
                  FROM 
                    @ProcedureSearchPhrases PP
                    INNER JOIN  [MASTERCPT] AS CPT
                      ON CPT.[LEVEL] LIKE PP.[LEVEL] + ''%''
                  WHERE 
                      CPT.[CODE] IS NOT NULL
                      AND CPT.[CODE] NOT IN (''0'', '''')
                    AND CPT.[CODE] NOT IN (SELECT CPTE.[CODE] FROM CPT AS CPTE WHERE CPTE.[CODE] IS NOT NULL)

                  UNION ALL

                  SELECT 
                      LTRIM(RTRIM(CPT.[CODE])) AS procedureCode, 
                      LTRIM(RTRIM(CPT.[LEVEL])) AS description, 
                      LTRIM(RTRIM(CPT.[COMBO])) AS category,
                      LTRIM(RTRIM(CPT.[CHARGE])) AS charge,
                      ''True'' AS active,
                      LTRIM(RTRIM([RVU])) AS rvu,
                      ''0CPTMore'' AS sourceTable,
                      ''04'' AS sequenceSet
                FROM 
                    @ProcedureSearchPhrases PP
                    INNER JOIN  [CPTMORE] AS CPT
                      ON CPT.[LEVEL] LIKE ''%'' + PP.[LEVEL] + ''%''
                  WHERE 
                      (CPT.[COMBO] IS NULL OR CPT.[COMBO] NOT IN (''Editor'',''MOD'',''CATEGORY'',''Types'',''Bundles''))
                      AND CPT.[CODE] IS NOT NULL
                      AND CPT.[CODE] NOT IN (''0'', '''')
                    AND CPT.[CODE] NOT IN (SELECT CPTE.[CODE] FROM CPT AS CPTE WHERE CPTE.[CODE] IS NOT NULL)

                  UNION ALL

                  SELECT 
                      LTRIM(RTRIM(CPT.[CODE])) AS procedureCode, 
                      LTRIM(RTRIM(CPT.[LEVEL])) AS description, 
                      LTRIM(RTRIM(CPT.[CATEGORY])) AS category,
                      LTRIM(RTRIM(CPT.[CHARGE])) AS charge,
                      COALESCE(CASE [ACTIVE] WHEN 1 THEN ''True'' WHEN 0 THEN ''False'' WHEN '''' THEN ''False'' ELSE ''False'' END,''True'') AS active,
                      LTRIM(RTRIM([RVU])) AS rvu,
                      ''2MasterCPT'' AS sourceTable,
                      ''04'' AS sequenceSet
                  FROM 
                    @ProcedureSearchPhrases PP
                    INNER JOIN  [MASTERCPT] AS CPT
                      ON CPT.[LEVEL] LIKE ''%'' + PP.[LEVEL] + ''%''
                  WHERE 
                      CPT.[CODE] IS NOT NULL
                      AND CPT.[CODE] NOT IN (''0'', '''')
                    AND CPT.[CODE] NOT IN (SELECT CPTE.[CODE] FROM CPT AS CPTE WHERE CPTE.[CODE] IS NOT NULL)

             ) AS CPTS 

            ORDER BY 
                 sequenceSet, sourceTable, [description]

        END

        /* Final select - uses artificial ordering from the insertion ORDER BY */
        SELECT procedureCode, description,  category, rvu, charge, active FROM
        ( 
        SELECT TOP 500 *-- procedureCode, description,  category, rvu, charge, active
        FROM #DistinctMatchingCpts
        ORDER BY sequenceSet, sourceTable, description

        ) AS CPTROWS

        DROP TABLE #DistinctMatchingCpts

However, this does NOT meet the criteria of best match on the count of words (as in the row 1 value in the sample) which should match the best (most) words found count from that row.

I have complete control over the form/format of the table value parameter if that makes a difference.

I am returning this result to a c# program if that is useful.

Mark Schultheiss
  • 32,614
  • 12
  • 69
  • 100
  • did any of these answers your question? – KM. Jul 13 '11 at 18:17
  • Several answers, some ideas but none fully sufficed to get a complete result set meeting the criteria list. At present I am prototyping an algorithm that seems to be doing what I want - once I have it fully vetted I will determine if it is a viable solution meeting those goals. – Mark Schultheiss Jul 14 '11 at 12:03

4 Answers4

5

You need to be able to split strings to solve this problem. I prefer the number table approach to split a string in TSQL

For my code below to work (as well as my split function), you need to do this one time table setup:

SELECT TOP 10000 IDENTITY(int,1,1) AS Number
    INTO Numbers
    FROM sys.objects s1
    CROSS JOIN sys.objects s2
ALTER TABLE Numbers ADD CONSTRAINT PK_Numbers PRIMARY KEY CLUSTERED (Number)

Once the Numbers table is set up, create this split function:

CREATE FUNCTION [dbo].[FN_ListToTable]
(
     @SplitOn  char(1)      --REQUIRED, the character to split the @List string on
    ,@List     varchar(8000)--REQUIRED, the list to split apart
)
RETURNS TABLE
AS
RETURN 
(

    ----------------
    --SINGLE QUERY-- --this will not return empty rows
    ----------------
    SELECT
        ListValue
        FROM (SELECT
                  LTRIM(RTRIM(SUBSTRING(List2, number+1, CHARINDEX(@SplitOn, List2, number+1)-number - 1))) AS ListValue
                  FROM (
                           SELECT @SplitOn + @List + @SplitOn AS List2
                       ) AS dt
                      INNER JOIN Numbers n ON n.Number < LEN(dt.List2)
                  WHERE SUBSTRING(List2, number, 1) = @SplitOn
             ) dt2
        WHERE ListValue IS NOT NULL AND ListValue!=''

);
GO

Feel free to make your own split function, but you still need the Numbers table for my solution to work.

You can now easily split a CSV string into a table and join on it:

select * from dbo.FN_ListToTable(',','1,2,3,,,4,5,6777,,,')

OUTPUT:

ListValue
-----------------------
1
2
3
4
5
6777

(6 row(s) affected)

now try this:

DECLARE @BaseTable table (RowID int primary key, RowValue varchar(100))
set nocount on
INSERT @BaseTable VALUES ( 1,'The cows came home empty handed')
INSERT @BaseTable VALUES ( 2,'This is my string as a test template to rank')                           -- third
INSERT @BaseTable VALUES ( 3,'pencil pen paperclip eraser')
INSERT @BaseTable VALUES ( 4,'wow')
INSERT @BaseTable VALUES ( 5,'no dice here')
INSERT @BaseTable VALUES ( 6,'This is my string as a test template to rank on even though not exact.') -- second
INSERT @BaseTable VALUES ( 7,'apple banana pear grape lemon orange kiwi strawberry peach watermellon')
INSERT @BaseTable VALUES ( 8,'This is my string as a test template')                                   -- 5th
INSERT @BaseTable VALUES ( 9,'rat cat bat mat sat fat hat pat ')
INSERT @BaseTable VALUES (10,'house home pool roll')
INSERT @BaseTable VALUES (11,'This is my string as a test template to')                                -- 4th
INSERT @BaseTable VALUES (12,'talk wisper yell scream sing hum')
INSERT @BaseTable VALUES (13,'This is my string as a test template to rank on.')                       -- first
INSERT @BaseTable VALUES (14,'aaa bbb ccc ddd eee fff ggg hhh')
INSERT @BaseTable VALUES (15,'three twice three once twice three')
set nocount off

DECLARE @SearchValue varchar(100)
SET @SearchValue='This is my value string as a test template to rank on.'

;WITH SplitBaseTable AS --expand each @BaseTable row into one row per word
(SELECT
     b.RowID, b.RowValue, s.ListValue
     FROM @BaseTable b
         CROSS APPLY  dbo.FN_ListToTable(' ',b.RowValue) AS s
)
, WordMatchCount AS --for each @BaseTable row that has has a word in common withe the search string, get the count of matching words
(SELECT
     s.RowID,COUNT(*) AS CountOfWordMatch
     FROM dbo.FN_ListToTable(' ',@SearchValue) v
         INNER JOIN SplitBaseTable             s ON v.ListValue=s.ListValue
     GROUP BY s.RowID
     HAVING COUNT(*)>0
)
, SearchLen AS --get one row for each possible length of the search string
(
SELECT
    n.Number,SUBSTRING(@SearchValue,1,n.Number) AS PartialSearchValue
    FROM Numbers n
    WHERE n.Number<=LEN(@SearchValue)
)
, MatchLen AS --for each @BaseTable row, get the max starting length that matches the search string
(
 SELECT
     b.RowID,MAX(l.Number) MatchStartLen
     FROM @BaseTable                 b
         LEFT OUTER JOIN SearchLen   l ON LEFT(b.RowValue,l.Number)=l.PartialSearchValue
     GROUP BY b.RowID
)
SELECT --return the final search results
    b.RowValue,w.CountOfWordMatch,m.MatchStartLen
    FROM @BaseTable                     b
        LEFT OUTER JOIN WordMatchCount  w ON b.RowID=w.RowID
        LEFT OUTER JOIN MatchLen        m ON b.RowID=m.RowID
    WHERE w.CountOfWordMatch>0
    ORDER BY w.CountOfWordMatch DESC,m.MatchStartLen DESC,LEN(b.RowValue) DESC,b.RowValue ASC

OUTPUT:

RowValue                                                                CountOfWordMatch MatchStartLen
----------------------------------------------------------------------- ---------------- -------------
This is my string as a test template to rank on.                        11               11
This is my string as a test template to rank on even though not exact.  10               11
This is my string as a test template to rank                            10               11
This is my string as a test template to                                 9                11
This is my string as a test template                                    8                11

(5 row(s) affected)

It does the start of the string word matches a little different, in that it looks at the number of characters from the beginning of the string that match.

Once you get this working, you can try to optimize it by creating some static indexed tables for SplitBaseTable. Possibly use a trigger on your @BaseTable.

KM.
  • 101,727
  • 34
  • 178
  • 212
  • This is an interesting idea. Current challenges are: not a single word separator as: )(,/<;& space<>=%-][ all exist in and are somewhat significant as in the phrases in double quotes: " Level > 9.0%" or "LDL-C <100 mg/dl", "LDL-C 100-129 mg/dl", "LDL-C = 130 mg/dl" SO I will have to figure a way through that issue, perhaps by multiple separators to create the static index as you suggest for a "word/phrase" list, further complicated by no unique key on the base table in use. – Mark Schultheiss Jul 07 '11 at 14:22
  • You have three options in regards to the "crazy" word/phrase split rules: 1) write a CLR split routine to handle all the logic necessary. 2) insert a single character like `PRINT CHAR(182)` within your strings to clearly identify splits. 3) redesign the table so each "phrase" is already split into its own row, and they can be reconstructed based on a ID and a sequence number. As for the primary key, add an identity column as shown here: http://blog.sqlauthority.com/2009/05/03/sql-server-add-or-remove-identity-property-on-column/ and make it the PK – KM. Jul 07 '11 at 19:12
  • Great solution! I am using this approach and it works very well. Thank you! But is it possible to display results with similar words as well? I'm not talking about levensthein distance. The LIKE Operator will do it for me... But I don't have an idea how to extend this solution. – Marc Sep 02 '15 at 10:48
0

It sounds like you are looking for a matching algorithm, which may be difficult to create without the use of stored procedures. From past experience, there are edit distance algorithms (such as Levenshtein) which are very useful in determining similarity. These return an number, sometimes a number of differences between strings, on which you can create your own weighting equation to give a score. You can then create rankings or thresholds for scores to lower false negatives/positives.

Kevin
  • 55
  • 1
  • 4
  • Somewhat like that, but the terms are VERY specific and maintain a limited set of words - all specific so an "exactly matches" is sufficient for my purposes, just need to get several "sets" of exact matches based on priority as described. Good suggestion though. – Mark Schultheiss Jun 27 '11 at 17:05
  • I also can use stored procedures or whatever I need to achieve a proper result set. – Mark Schultheiss Jul 04 '11 at 15:10
  • Good suggestion with the Levenshtein distance, however it is for letters, to be used in comparing terms. Now here's the fun part: this is the correct answer: Levenshtein distance. What you are trying to achieve is the Levenshtein distance, but not in letters, but in words. I suggest that if it's possible, to make a CLR assembly for calculating this distance. A word-Levenshtein distance of 1 would mean that one word (not letter) is not in its place (it's not supposed to be there, or it's missing). So you could easily order by this distance. – Alex Jul 11 '11 at 00:21
0

I had a similar question a while back. The question I was trying to answer was how many words matched between two different columns, and rank based on the highest percentage of words matched. It was well beyond me but I got a fantastic answer from Martin.

Check out his answer to my question here.

Community
  • 1
  • 1
Mr Moose
  • 5,946
  • 7
  • 34
  • 69
0

One answer to all your issues: use sphynx http://sphinxsearch.com and don't solve this in SQL.

Sphynx is open source, works with all databases and all operating systems.

That's what craigslist is using.

It's the best outside full text search system at the time of this post. It will order your results in the relevance you are asking for and you won't need fancy SQL tables or SQL procedures. Try it.

Max
  • 1,049
  • 7
  • 9
  • Sometimes, for better or worse, you have to retrieve the records using SQL (for example, if you are also filtering them based on other criteria, and if you are linking them to related tables). – Jordan Reiter Nov 20 '13 at 19:16