Priority SQL Full Text Search Results

Question

I use Lucene for full-text search, and it works fine, but my main database was SQL. I did not like the idea of ​​having two mechanisms for the database and decided to use full-text search in SQL. Everything works fine, but there are some more things that I did not understand. Let's say I have a table below:

All fields are indexed for full-text search.

Now I want to do a full text search in this table with the text "Isaac." And I would like row 5 to be on top and the rest of the results to be lower. Can I determine how to set the priority for a field so that everything in this field remains at the top of the result? In principle, I would like to prioritize this field. In my real table, I have 6 fields.

Answer 1

As far as I know, MSSQL does not support string affinity comparison. You must 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 add it to your order

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

Adapt it to DTB, but this should work for you.

---

Or you can set a simple CASE WHEN clause to create an 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