Fuzzy search of stored procedures and sorting by matching rate

There is a search function in the recent project. In this case, you can simply do the search, and you can simply like the percent sign. But after thinking about it, I had to do something very tall, and I was not very familiar with the stored procedure. So I decided to make it bigger. In the past, I did not think so much about the search, but after all, the project users are a large number of users. In order to increase the user experience, we should provide users with the most desired search results, this involves sorting, and sorting by matching rate. It can also be said to be accurate. I cannot find a suitable one if I find it online. So I wrote it myself.

First, let's talk about my thinking principle: Step 1: Separate the keywords to be searched and perform Fuzzy Matching separately. For example, "I am a Chinese" is divided into "I", "I am", "I am a Chinese", and "I am a Chinese ", match these keywords respectively. In the second step, the matching results are allocated to the corresponding fields and sorted by the longest keywords to get the desired results.

The logic for keyword allocation is originally intended to be written to the program code, and then the SQL statement is dynamically generated and sent to the database for execution. However, I didn't know where to see it before, this means that the network throughput will be increased. (You can tell me what you know .). Therefore, the entire logic is written into the stored procedure.

Run the SQL code below.

1 create proc [DBO]. [sp_fuzzy_search] 2 @ keyword nvarchar (15) -- limit the keyword to 15 characters. Mainly for performance reasons. Furthermore, matching a search term can meet users' search needs. 3 @ tablename varchar (20), -- indicates 4 @ cellname varchar (20) to be searched -- the field to be matched. A field is written here. Matching of multiple fields is not within the scope of consideration. 5 As 6 begin 7 declare @ SQL nvarchar (500), @ num int, @ I int, @ orderby varchar (200) 8 set @ I = 0 9 set @ num = Len (@ keyword) 10 set @ SQL = 'select ID, '+ @ cellname +' from (select ID, '+ @ cellname +', '11 set @ orderby = ''12 while @ I <@ num -- Obtain keywords cyclically. Here, we use the longest keyword for loop, which is conducive to sorting. 13 begin14 set @ SQL + = 'case when patindex (''% '+ substring (@ keyword, 1, @ num) +' % '', '+ @ cellname +')> 0 then 1 else 0 end as t' + convert (varchar, @ num) + ', '15 set @ orderby + = 'T' + convert (varchar, @ num) + 'DESC, '16 set @ num-= 117 18 end19 -- concatenate SQL statement 20 set @ SQL = left (@ SQL, Len (@ SQL)-1) + 'from' + @ tablename + ') as TT where T1> 0 order by' + Left (@ orderby, Len (@ orderby)-1) 21 -- execute the spliced SQL statement. 22 exec sp_executesql @ sql23 24 end

Test data, for example:

 

Search for the keyword "Chinese". The returned value is as follows:

The idea is like this. It seems a bit of a bug. You may have good comments or suggestions from the official team. Please leave us blank. Thank you.