SQL Server 2008 - 高级搜索/排序 [英] SQL Server 2008 - Advanced Search/Sorting

问题描述

我需要在列上进行搜索并按特定顺序对结果进行排序。搜索条件和排序顺序如下：

给定搜索文本中至少有'x'和至多全部字符必须匹配。结果应该按照开头（然后按匹配的字符数进行排序），然后按包含（然后按匹配和按字母顺序排列的字符数）进行分组。

例如： / p>

搜索文本：联盟A

strong>

搜索结果应按以下顺序显示：

有人可以帮我吗？

解决方案

您正在寻找 Levenshtein距离 p>

这里是T-SQL的算法实现：计算Levens因为函数签名是这样的：

 <$ c 
 
 
 
 $ c $> CREATE FUNCTION [dbo]。[LEVENSHTEIN]（@s NVARCHAR（MAX），@t NVARCHAR（MAX））
 / * 
 Levenshtein距离算法：TSQL实现
由Joseph Gama 
 
 http://www.merriampark.com/ldtsql.htm 
  

您可以在查询中调用它：

 从ValuesInDatabase中选择* 
 V 
 order by 
 [dbo]。[LEVENSHTEIN]（V.ClientName，'Alliance A'）
  

'b'b

编辑

我在这里复制Joseph Gama的功能来保存它，不要忘记访问 devioblog ：

  CREATE FUNCTION [dbo]。[LEVENSHTEIN]（@s NVARCHAR（MAX），@t NVARCHAR（MAX））
 / * 
 Levenshtein距离算法： TSQL执行
由Joseph Gama 
 
 http://www.merriampark.com/ldtsql.htm 
 
返回字符串s1和s2之间的Levenshtein距离。 
原始开发者：Michael Gilleland http://www.merriampark.com/ld.htm 
由Joseph Gama翻译为TSQL 
 
由Herbert Oppolzer修正/ devio 
详见http://devio.wordpress.com/2010/09/07/calculating-levenshtein-distance-in-tsql 
 * / 
 RETURNS INT AS 
 BEGIN 
 DECLARE @d NVARCHAR（MAX），@LD INT，@m INT，@n INT，@i INT，@j INT，
 @s_i NCHAR（1），@t_j NCHAR（1），@ cost INT 
 
  - 步骤1 
 SET @n = LEN（@s）
 SET @m = LEN（@t）
 SET @d = REPLICATE（NCHAR（0 ），（@ n + 1）*（@ m + 1））
 IF @n = 0 
 BEGIN 
 SET @LD = @m 
 GOTO done 
 END 
 IF @m = 0 
 BEGIN 
 SET @LD = @n 
 GOTO done 
 END 
 
  - 第2步
 SET @i = 0 
 WHILE @i< = @n BEGIN 
 SET @d = STUFF（@ d，@ i + 1,1，NCHAR（@i）） -  d（ i，0）= i 
 SET @i = @ i + 1 
 END 
 
 SET @i = 0 
 WHILE @i< = @m BEGIN 
 SET @d = STUFF（@ d，@ i *（@ n + 1）+ 1,1，NCHAR（@i）） -  d（0，j）= j 
 SET @i = @ i + 1 
 END 
 
  - 步骤3 
 SET @i = 1 
 WHILE @i <= @n BEGIN 
 SET @s_i = SUBSTRING（@ s，@ i，1）
 
  - 步骤4 
 SET @j = 1 
 WHILE @j <= @m BEGIN 
 SET @t_j = SUBSTRING（@ t，@ j，1）
  - 步骤5 
 IF @s_i = @t_j 
 SET @cost = 0 
 ELSE 
 SET @cost = 1 
  - 步骤6 
 SET @d = STUFF（@ d，@ j *（@ n + 1）+ @ i + 1,1， 
 NCHAR（dbo.MIN3（
 UNICODE（SUBSTRING（@ d，@ j *（@ n + 1）+ @ i-1 + 1,1））+ 1，
 UNICODE SUBSTRING（@d，（@ j-1）*（@ n + 1）+ @ i + 1,1））+ 1，
 UNICODE（SUBSTRING（@d，（@ j-1）*（@ 
 SET @j = @ j + 1 
 END 
 SET @i = 1 + 1）+ @ cost = 
） @ i + 1 
 END 
 
  - 步骤7 
 SET @LD = UNICODE（SUBSTRING（@ d，@ n *（@ m + 1）+ @ m + 1 ，1））
 
完成：
 RETURN @LD 
 END 
  

I need to conduct a search on a column and sort the results in a specific order. The search criteria and the sort order is going to be as following:

At least 'x' and at most all of the characters for a given search text must be matched. Result should be grouped by begins with (then sort by number of characters matched) followed by contains (then sort by number of characters matched and alphabetically).

For Example:

Search Text: Alliance A

Values in Database:

The search results should appear in the following order

Can someone please help me out?

解决方案

You are looking for Levenshtein distance

Here an algorithm implementation for T-SQL: Calculating Levenshtein Distance in TSQL

As function signature is like:

CREATE FUNCTION [dbo].[LEVENSHTEIN]( @s NVARCHAR(MAX), @t NVARCHAR(MAX) )
/*
Levenshtein Distance Algorithm: TSQL Implementation
by Joseph Gama

http://www.merriampark.com/ldtsql.htm

You can invoke it in your query as:

Select *
from ValuesInDatabase V
order by
    [dbo].[LEVENSHTEIN](  V.ClientName, 'Alliance A' ) 

You don't should expect high performance.

Edited

I copy here Joseph Gama's function to preserve it, don't forget visit devioblog:

CREATE FUNCTION [dbo].[LEVENSHTEIN]( @s NVARCHAR(MAX), @t NVARCHAR(MAX) )
/*
Levenshtein Distance Algorithm: TSQL Implementation
by Joseph Gama

http://www.merriampark.com/ldtsql.htm

Returns the Levenshtein Distance between strings s1 and s2.
Original developer: Michael Gilleland http://www.merriampark.com/ld.htm
Translated to TSQL by Joseph Gama

Fixed by Herbert Oppolzer / devio
as described in http://devio.wordpress.com/2010/09/07/calculating-levenshtein-distance-in-tsql
*/
RETURNS INT AS
BEGIN
  DECLARE @d NVARCHAR(MAX), @LD INT, @m INT, @n INT, @i INT, @j INT,
    @s_i NCHAR(1), @t_j NCHAR(1),@cost INT

  --Step 1
  SET @n = LEN(@s)
  SET @m = LEN(@t)
  SET @d = REPLICATE(NCHAR(0),(@n+1)*(@m+1))
  IF @n = 0
  BEGIN
    SET @LD = @m
   GOTO done
  END
  IF @m = 0
  BEGIN
    SET @LD = @n
    GOTO done
  END

  --Step 2
  SET @i = 0
  WHILE @i <= @n BEGIN
    SET @d = STUFF(@d,@i+1,1,NCHAR(@i))        --d(i, 0) = i
    SET @i = @i+1
  END

  SET @i = 0
  WHILE @i <= @m BEGIN
    SET @d = STUFF(@d,@i*(@n+1)+1,1,NCHAR(@i))    --d(0, j) = j
    SET @i = @i+1
  END

  --Step 3
  SET @i = 1
  WHILE @i <= @n BEGIN
    SET @s_i = SUBSTRING(@s,@i,1)

    --Step 4
    SET @j = 1
    WHILE @j <= @m BEGIN
      SET @t_j = SUBSTRING(@t,@j,1)
      --Step 5
      IF @s_i = @t_j
        SET @cost = 0
      ELSE
        SET @cost = 1
      --Step 6
      SET @d = STUFF(@d,@j*(@n+1)+@i+1,1,
        NCHAR(dbo.MIN3(
          UNICODE(SUBSTRING(@d,@j*(@n+1)+@i-1+1,1))+1,
          UNICODE(SUBSTRING(@d,(@j-1)*(@n+1)+@i+1,1))+1,
          UNICODE(SUBSTRING(@d,(@j-1)*(@n+1)+@i-1+1,1))+@cost)
        ))
      SET @j = @j+1
    END
    SET @i = @i+1
  END      

  --Step 7
  SET @LD = UNICODE(SUBSTRING(@d,@n*(@m+1)+@m+1,1))

done:
  RETURN @LD
END

这篇关于SQL Server 2008 - 高级搜索/排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！