SQL Full Text Search result priority

Question

I am used to use Lucene for full text search and it was working great, but my main database was SQL. I didn't like the idea of having two mechanism for database and decided to use SQL Full Text Search. Things working great, but there are still some things that I haven't figured out. Lets say I have the table below:

All the fields are indexed for full text search.

Now I want to do full text search on this table with text "Isaac". And I would like the row 5 to be on the top and rest of the results to be below that. Can I some how set a priority to a field so that anything that is found on that field will stays at the top of the result? Basically I would like to prioritize it by the field. In my real table I have 6 fields.

Answer 1

As far as I know MSSQL doesn't support string similarity comparison .. you have to write your own set of functions

-- get percentage diff
CREATE FUNCTION [dbo].[GetPercentageOfTwoStringMatching]
(
    @string1 NVARCHAR(100)
    ,@string2 NVARCHAR(100)
)
RETURNS INT
AS
BEGIN

    DECLARE @levenShteinNumber INT

    DECLARE @string1Length INT = LEN(@string1)
    , @string2Length INT = LEN(@string2)
    DECLARE @maxLengthNumber INT = CASE WHEN @string1Length > @string2Length THEN @string1Length ELSE @string2Length END

    SELECT @levenShteinNumber = [dbo].[LEVENSHTEIN] (   @string1  ,@string2)

    DECLARE @percentageOfBadCharacters INT = @levenShteinNumber * 100 / @maxLengthNumber

    DECLARE @percentageOfGoodCharacters INT = 100 - @percentageOfBadCharacters

    -- Return the result of the function
    RETURN @percentageOfGoodCharacters

END

-- get diff of strings 
CREATE FUNCTION [dbo].[LEVENSHTEIN](@left  VARCHAR(100),
                                    @right VARCHAR(100))
returns INT
AS
  BEGIN
      DECLARE @difference    INT,
              @lenRight      INT,
              @lenLeft       INT,
              @leftIndex     INT,
              @rightIndex    INT,
              @left_char     CHAR(1),
              @right_char    CHAR(1),
              @compareLength INT

      SET @lenLeft = LEN(@left)
      SET @lenRight = LEN(@right)
      SET @difference = 0

      IF @lenLeft = 0
        BEGIN
            SET @difference = @lenRight

            GOTO done
        END

      IF @lenRight = 0
        BEGIN
            SET @difference = @lenLeft

            GOTO done
        END

      GOTO comparison

      COMPARISON:

      IF ( @lenLeft >= @lenRight )
        SET @compareLength = @lenLeft
      ELSE
        SET @compareLength = @lenRight

      SET @rightIndex = 1
      SET @leftIndex = 1

      WHILE @leftIndex <= @compareLength
        BEGIN
            SET @left_char = substring(@left, @leftIndex, 1)
            SET @right_char = substring(@right, @rightIndex, 1)

            IF @left_char <> @right_char
              BEGIN -- Would an insertion make them re-align?
                  IF( @left_char = substring(@right, @rightIndex + 1, 1) )
                    SET @rightIndex = @rightIndex + 1
                  -- Would an deletion make them re-align?
                  ELSE IF( substring(@left, @leftIndex + 1, 1) = @right_char )
                    SET @leftIndex = @leftIndex + 1

                  SET @difference = @difference + 1
              END

            SET @leftIndex = @leftIndex + 1
            SET @rightIndex = @rightIndex + 1
        END

      GOTO done

      DONE:

      RETURN @difference
  END 

and then you will add this to your order by

SELECT *
FROM [dbo].[some_table]
ORDER BY [dbo].[GetPercentageOfTwoStringMatching](col1 ,col2) DESC

Adapt it to your DTB but this should work for you

---

Or you can set simple CASE WHEN condition to create order column

SELECT *
FROM [dbo].[table]
ORDER BY
        CASE
          WHEN str = 'search_string' THEN 1
          WHEN str LIKE '%search_string%' THEN 2
          ELSE 3
        END