blocks|key|1623802|text|在SQL+Server2005和更高版本中，您可以用任何.NET语言编写存储过程：Using+CLR+Integration+in+SQL+Server+2005。这样一来，编写一个计算Levenstein+distance的过程应该不难。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1623803|一个简单的Hello，World！摘自帮助：|1623804|using+System;
using+System.Data;
using+Microsoft.SqlServer.Server;
using+System.Data.SqlTypes;

public+class+HelloWorldProc
{
++++[Microsoft.SqlServer.Server.SqlProcedure]
++++public+static+void+HelloWorld(out+string+text)
++++{
++++++++SqlContext.Pipe.Send("Hello+world!"+%2B+Environment.NewLine);
++++++++text+=+"Hello+world!";
++++}
}|code-block|syntax|javascript|1623805|然后在SQL+Server中运行以下命令：|1623806|CREATE+ASSEMBLY+helloworld+from+'c:\helloworld.dll'+WITH+PERMISSION_SET+=+SAFE

CREATE+PROCEDURE+hello
@i+nchar(25)+OUTPUT
AS
EXTERNAL+NAME+helloworld.HelloWorldProc.HelloWorld|1623807|现在你可以测试运行它了：|1623808|DECLARE+@J+nchar(25)
EXEC+hello+@J+out
PRINT+@J|1623809|希望这能有所帮助。|1623810|entityMap|0|LINK|mutability|MUTABLE|url|http://msdn.microsoft.com/en-us/library/ms345136.aspx|1|http://en.wikipedia.org/wiki/Levenshtein_distance^0|15|14|0|2L|J|1|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|14|8|@]|9|@$A|15|B|16|1|17]|$A|18|B|19|1|1A]]|C|$]]|$1|D|3|E|5|6|7|1B|8|@]|9|@]|C|$]]|$1|F|3|G|5|H|7|1C|8|@]|9|@]|C|$I|J]]|$1|K|3|L|5|6|7|1D|8|@]|9|@]|C|$]]|$1|M|3|N|5|H|7|1E|8|@]|9|@]|C|$I|J]]|$1|O|3|P|5|6|7|1F|8|@]|9|@]|C|$]]|$1|Q|3|R|5|H|7|1G|8|@]|9|@]|C|$I|J]]|$1|S|3|T|5|6|7|1H|8|@]|9|@]|C|$]]|$1|U|3|-4|5|6|7|1I|8|@]|9|@]|C|$]]]|V|$W|$5|X|Y|Z|C|$10|11]]|12|$5|X|Y|Z|C|$10|13]]]]

IIRC, with SQL Server 2005 and later you can write stored procedures in any .NET language: <a href="http://msdn.microsoft.com/en-us/library/ms345136.aspx" rel="noreferrer">Using CLR Integration in SQL Server 2005</a>. With that it shouldn't be hard to write a procedure for calculating <a href="http://en.wikipedia.org/wiki/Levenshtein_distance" rel="noreferrer">Levenstein distance</a>.

A simple Hello, World! extracted from the help:

<pre><code>using System;
using System.Data;
using Microsoft.SqlServer.Server;
using System.Data.SqlTypes;

public class HelloWorldProc
{
 [Microsoft.SqlServer.Server.SqlProcedure]
 public static void HelloWorld(out string text)
 {
 SqlContext.Pipe.Send("Hello world!" + Environment.NewLine);
 text = "Hello world!";
 }
}
</code></pre>

Then in your SQL Server run the following:

<pre><code>CREATE ASSEMBLY helloworld from 'c:\helloworld.dll' WITH PERMISSION_SET = SAFE

CREATE PROCEDURE hello
@i nchar(25) OUTPUT
AS
EXTERNAL NAME helloworld.HelloWorldProc.HelloWorld
</code></pre>

And now you can test run it:

<pre><code>DECLARE @J nchar(25)
EXEC hello @J out
PRINT @J
</code></pre>

Hope this helps.

blocks|key|1409169|text|您可以使用Levenshtein距离算法来比较字符串|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1409170|在这里，您可以在http://www.kodyaz.com/articles/fuzzy-string-matching-using-levenshtein-distance-sql-server.aspx找到一个T-SQL示例|offset|length|1409171|CREATE+FUNCTION+edit_distance(@s1+nvarchar(3999),+@s2+nvarchar(3999))
RETURNS+int
AS
BEGIN
+DECLARE+@s1_len+int,+@s2_len+int
+DECLARE+@i+int,+@j+int,+@s1_char+nchar,+@c+int,+@c_temp+int
+DECLARE+@cv0+varbinary(8000),+@cv1+varbinary(8000)

+SELECT
++@s1_len+=+LEN(@s1),
++@s2_len+=+LEN(@s2),
++@cv1+=+0x0000,
++@j+=+1,+@i+=+1,+@c+=+0

+WHILE+@j+<=+@s2_len
++SELECT+@cv1+=+@cv1+%2B+CAST(@j+AS+binary(2)),+@j+=+@j+%2B+1

+WHILE+@i+<=+@s1_len
+BEGIN
++SELECT
+++@s1_char+=+SUBSTRING(@s1,+@i,+1),
+++@c+=+@i,
+++@cv0+=+CAST(@i+AS+binary(2)),
+++@j+=+1

++WHILE+@j+<=+@s2_len
++BEGIN
+++SET+@c+=+@c+%2B+1
+++SET+@c_temp+=+CAST(SUBSTRING(@cv1,+@j%2B@j-1,+2)+AS+int)+%2B
++++CASE+WHEN+@s1_char+=+SUBSTRING(@s2,+@j,+1)+THEN+0+ELSE+1+END
+++IF+@c+>+@c_temp+SET+@c+=+@c_temp
+++SET+@c_temp+=+CAST(SUBSTRING(@cv1,+@j%2B@j%2B1,+2)+AS+int)%2B1
+++IF+@c+>+@c_temp+SET+@c+=+@c_temp
+++SELECT+@cv0+=+@cv0+%2B+CAST(@c+AS+binary(2)),+@j+=+@j+%2B+1
+END

+SELECT+@cv1+=+@cv0,+@i+=+@i+%2B+1
+END

+RETURN+@c
END|code-block|syntax|javascript|1409172|(+Joseph+Gama开发的函数)|1409173|用法：|1409174|select
+dbo.edit_distance('Fuzzy+String+Match','fuzzy+string+match'),
+dbo.edit_distance('fuzzy','fuzy'),
+dbo.edit_distance('Fuzzy+String+Match','fuzy+string+match'),
+dbo.edit_distance('levenshtein+distance+sql','levenshtein+sql+server'),
+dbo.edit_distance('distance','server')|1409175|该算法只返回stpe计数，通过在一步中替换不同的字符来将一个字符串更改为另一个字符串|1409176|entityMap|0|LINK|mutability|MUTABLE|url|http://www.kodyaz.com/articles/fuzzy-string-matching-using-levenshtein-distance-sql-server.aspx^0|0|8|2N|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|10|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|11|8|@]|9|@$D|12|E|13|1|14]]|A|$]]|$1|F|3|G|5|H|7|15|8|@]|9|@]|A|$I|J]]|$1|K|3|L|5|6|7|16|8|@]|9|@]|A|$]]|$1|M|3|N|5|6|7|17|8|@]|9|@]|A|$]]|$1|O|3|P|5|H|7|18|8|@]|9|@]|A|$I|J]]|$1|Q|3|R|5|6|7|19|8|@]|9|@]|A|$]]|$1|S|3|-4|5|6|7|1A|8|@]|9|@]|A|$]]]|T|$U|$5|V|W|X|A|$Y|Z]]]]

You can use Levenshtein Distance Algorithm for comparing strings

Here you can find a T-SQL example at <a href="http://www.kodyaz.com/articles/fuzzy-string-matching-using-levenshtein-distance-sql-server.aspx" rel="nofollow">http://www.kodyaz.com/articles/fuzzy-string-matching-using-levenshtein-distance-sql-server.aspx</a>

<pre><code>CREATE FUNCTION edit_distance(@s1 nvarchar(3999), @s2 nvarchar(3999))
RETURNS int
AS
BEGIN
 DECLARE @s1_len int, @s2_len int
 DECLARE @i int, @j int, @s1_char nchar, @c int, @c_temp int
 DECLARE @cv0 varbinary(8000), @cv1 varbinary(8000)

 SELECT
 @s1_len = LEN(@s1),
 @s2_len = LEN(@s2),
 @cv1 = 0x0000,
 @j = 1, @i = 1, @c = 0

 WHILE @j &lt;= @s2_len
 SELECT @cv1 = @cv1 + CAST(@j AS binary(2)), @j = @j + 1

 WHILE @i &lt;= @s1_len
 BEGIN
 SELECT
 @s1_char = SUBSTRING(@s1, @i, 1),
 @c = @i,
 @cv0 = CAST(@i AS binary(2)),
 @j = 1

 WHILE @j &lt;= @s2_len
 BEGIN
 SET @c = @c + 1
 SET @c_temp = CAST(SUBSTRING(@cv1, @j+@j-1, 2) AS int) +
 CASE WHEN @s1_char = SUBSTRING(@s2, @j, 1) THEN 0 ELSE 1 END
 IF @c &gt; @c_temp SET @c = @c_temp
 SET @c_temp = CAST(SUBSTRING(@cv1, @j+@j+1, 2) AS int)+1
 IF @c &gt; @c_temp SET @c = @c_temp
 SELECT @cv0 = @cv0 + CAST(@c AS binary(2)), @j = @j + 1
 END

 SELECT @cv1 = @cv0, @i = @i + 1
 END

 RETURN @c
END
</code></pre>

(Function developped by Joseph Gama)

Usage :

<pre><code>select
 dbo.edit_distance('Fuzzy String Match','fuzzy string match'),
 dbo.edit_distance('fuzzy','fuzy'),
 dbo.edit_distance('Fuzzy String Match','fuzy string match'),
 dbo.edit_distance('levenshtein distance sql','levenshtein sql server'),
 dbo.edit_distance('distance','server')
</code></pre>

The algorithm simply returns the stpe count to change one string into other by replacing a different character at one step

blocks|key|1623929|text|在TSQL中，比较两个项的最好、最快的方法是使用SELECT语句连接索引列上的表。因此，如果您想从RDBMS引擎的优势中获益，这就是我建议实现编辑距离的方式。TSQL循环也可以工作，但对于大容量比较，Levenstein距离计算在其他语言中比在TSQL中更快。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1623930|我已经在几个系统中实现了编辑距离，使用了一系列专门针对临时表的连接。它需要一些繁重的预处理步骤-准备临时表-但它在处理大量比较时非常有效。|1623931|简而言之:预处理包括创建、填充和索引临时表。第一个包含引用ids、一个字母列和一个charindex列。这个表是通过运行一系列insert查询(使用SELECT+SUBSTRING)将每个单词拆分成字母来填充的(使用SELECT+SUBSTRING)，以创建与源列表中的单词具有字母一样多的行(我知道，这是很多的行，但SQL+server可以处理数十亿行)。然后创建具有2个字母列的第二个表，以及具有3个字母列的另一个表，依此类推。最终结果是一系列表，其中包含每个单词的引用in和子字符串，以及它们在单词中位置的引用。|1623932|一旦完成，整个游戏就是复制这些表，并通过计算匹配数量的select查询将它们连接到一个组中。这为每对可能的单词创建了一系列度量，然后将这些度量重新聚合为每对单词的单个Levenstein距离。|1623933|从技术上讲，这与Levenstein距离(或其变体)的大多数其他实现非常不同，因此您需要深入了解Levenstein距离是如何工作的，以及为什么它是这样设计的。也要研究替代方法，因为使用这种方法，您最终会得到一系列底层指标，这些指标可以帮助同时计算编辑距离的许多变体，从而为您提供有趣的机器学习潜在改进。|1623934|本页前面的答案已经提到的另一点是:尝试尽可能多地进行预处理，以消除不需要距离测量的对。例如，应该排除没有单个字母的一对两个单词，因为编辑距离可以从字符串的长度获得。或者不测量同一单词的两个副本之间的距离，因为它本质上是0。或者在进行测量之前删除重复项，如果您的单词列表来自长文本，则相同的单词可能会多次出现，因此只测量一次距离将节省处理时间，等等。|1623935|entityMap^0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|N|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|O|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|P|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|Q|8|@]|9|@]|A|$]]|$1|H|3|I|5|6|7|R|8|@]|9|@]|A|$]]|$1|J|3|K|5|6|7|S|8|@]|9|@]|A|$]]|$1|L|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|M|$]]

In TSQL the best and fastest way to compare two items are SELECT statements that join tables on indexed columns. Therefore this is how I suggest to implement the editing distance if you want to benefit from the advantages of a RDBMS engine. TSQL Loops will work too, but Levenstein distance calculations will be faster in other languages than in TSQL for large volume comparisons. 

I have implemented the editing distance in several systems using series of Joins against temporary tables designed for that purpose only. It requires some heavy pre-processing steps - the preparation of the temporary tables - but it works very well with large number of comparisons. 

In a few words: the pre-processing consists of creating, populating and indexing temp tables. The first one contains reference ids, a one-letter column and a charindex column. This table is populated by running a series of insert queries that split every word into letters (using SELECT SUBSTRING) to create as many rows as word in the source list have letters (I know, that's a lot of rows but SQL server can handle billions of rows). Then make a second table with a 2-letter column, another table with a 3-letter column, etc. The end results is a series of tables which contain reference ids and substrings of each the words, as well a the reference of their position in the word.

Once this is done, the whole game is about duplicating these tables and joining them against their duplicate in a GROUP BY select query which counts the number of matches. This creates a series of measures for every possible pair of words, which are then re-aggregated into a single Levenstein distance per pair of words.

Technically this is very different than most other implementations of the Levenstein distance (or its variants) so you need to deeply understand how the Levenstein distance works and why it was designed as it is. Investigate the alternatives as well because with that method you end up with a series of underlying metrics which can help calculate many variants of the editing distance at the same time, providing you with interesting machine learning potential improvements. 

Another point already mentioned by previous answers in this page: try to pre process as much as possible to eliminate the pairs that do not require distance measurement. For example a pair of two words that have not a single letter in common should be excluded, because the editing distance can be obtained from the length of the strings. Or do not measure the distance between two copies of the same word, since it is 0 by nature. Or remove duplicates before doing the measurement, if your list of words comes from a long text it is likely that the same words will appear more than once, so measuring the distance only once will save processing time, etc.

blocks|key|1409277|text|My+mods+for+Azure+Synapse+(改为使用SET而不是SELECT)：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1409278|--+=============================================
--+Computes+and+returns+the+Levenshtein+edit+distance+between+two+strings,+i.e.+the
--+number+of+insertion,+deletion,+and+sustitution+edits+required+to+transform+one
--+string+to+the+other,+or+NULL+if+@max+is+exceeded.+Comparisons+use+the+case-
--+sensitivity+configured+in+SQL+Server+(case-insensitive+by+default).
--+
--+Based+on+Sten+Hjelmqvist's+"Fast,+memory+efficient"+algorithm,+described
--+at+http://www.codeproject.com/Articles/13525/Fast-memory-efficient-Levenshtein-algorithm,
--+with+some+additional+optimizations.
--+=============================================
CREATE+FUNCTION+[db0].[Levenshtein](
++++@s+nvarchar(4000)
++,+@t+nvarchar(4000)
++,+@max+int
)
RETURNS+int
WITH+SCHEMABINDING
AS
BEGIN
++++DECLARE+@distance+int+=+0+--+return+variable
++++++++++,+@v0+nvarchar(4000)--+running+scratchpad+for+storing+computed+distances
++++++++++,+@start+int+=+1++++++--+index+(1+based)+of+first+non-matching+character+between+the+two+string
++++++++++,+@i+int,+@j+int++++++--+loop+counters:+i+for+s+string+and+j+for+t+string
++++++++++,+@diag+int++++++++++--+distance+in+cell+diagonally+above+and+left+if+we+were+using+an+m+by+n+matrix
++++++++++,+@left+int++++++++++--+distance+in+cell+to+the+left+if+we+were+using+an+m+by+n+matrix
++++++++++,+@sChar+nchar++++++--+character+at+index+i+from+s+string
++++++++++,+@thisJ+int++++++++++--+temporary+storage+of+@j+to+allow+SELECT+combining
++++++++++,+@jOffset+int++++++--+offset+used+to+calculate+starting+value+for+j+loop
++++++++++,+@jEnd+int++++++++++--+ending+value+for+j+loop+(stopping+point+for+processing+a+column)
++++++++++--+get+input+string+lengths+including+any+trailing+spaces+(which+SQL+Server+would+otherwise+ignore)
++++++++++,+@sLen+int+=+datalength(@s)+/+datalength(left(left(@s,+1)+%2B+'.',+1))++++--+length+of+smaller+string
++++++++++,+@tLen+int+=+datalength(@t)+/+datalength(left(left(@t,+1)+%2B+'.',+1))++++--+length+of+larger+string
++++++++++,+@lenDiff+int++++++--+difference+in+length+between+the+two+strings
++++--+if+strings+of+different+lengths,+ensure+shorter+string+is+in+s.+This+can+result+in+a+little
++++--+faster+speed+by+spending+more+time+spinning+just+the+inner+loop+during+the+main+processing.
++++IF+(@sLen+>+@tLen)+BEGIN
++++++++SET+@v0+=+@s
++++++++SET+@i+=+@sLen+--+temporarily+use+v0+for+swap
++++++++SET+@s+=+@t
++++++++SET+@sLen+=+@tLen
++++++++SET+@t+=+@v0
++++++++SET+@tLen+=+@i
++++END
++++SET+@max+=+ISNULL(@max,+@tLen)
++++SET+@lenDiff+=+@tLen+-+@sLen

++++IF+@lenDiff+>+@max+RETURN+NULL

++++--+suffix+common+to+both+strings+can+be+ignored
++++WHILE(@sLen+>+0+AND+SUBSTRING(@s,+@sLen,+1)+=+SUBSTRING(@t,+@tLen,+1))
++++++++SET+@sLen+=+@sLen+-+1
++++++++SET+@tLen+=+@tLen+-+1

++++IF+(@sLen+=+0)+RETURN+@tLen

++++--+prefix+common+to+both+strings+can+be+ignored
++++WHILE+(@start+<+@sLen+AND+SUBSTRING(@s,+@start,+1)+=+SUBSTRING(@t,+@start,+1))+
++++++++SET+@start+=+@start+%2B+1
++++IF+(@start+>+1)+BEGIN
++++++++SET+@sLen+=+@sLen+-+(@start+-+1)
++++++++SET+@tLen+=+@tLen+-+(@start+-+1)

++++++++--+if+all+of+shorter+string+matches+prefix+and/or+suffix+of+longer+string,+then
++++++++--+edit+distance+is+just+the+delete+of+additional+characters+present+in+longer+string
++++++++IF+(@sLen+<=+0)+RETURN+@tLen

++++++++SET+@s+=+SUBSTRING(@s,+@start,+@sLen)
++++++++SET+@t+=+SUBSTRING(@t,+@start,+@tLen)
++++END

++++--+initialize+v0+array+of+distances
++++SET+@v0+=+''
++++SET+@j+=+1

++++WHILE+(@j+<=+@tLen)+BEGIN
++++++++SET+@v0+=+@v0+%2B+NCHAR(CASE+WHEN+@j+>+@max+THEN+@max+ELSE+@j+END)
++++++++SET+@j+=+@j+%2B+1
++++END

++++SET+@jOffset+=+@max+-+@lenDiff
++++SET+@i+=+1

++++WHILE+(@i+<=+@sLen)+BEGIN
++++++++SET+@distance+=+@i
++++++++SET+@diag+=+@i+-+1
++++++++SET+@sChar+=+SUBSTRING(@s,+@i,+1)
+++++++++++++--+no+need+to+look+beyond+window+of+upper+left+diagonal+(@i)+%2B+@max+cells
+++++++++++++--+and+the+lower+right+diagonal+(@i+-+@lenDiff)+-+@max+cells
++++++++SET+@j+=+CASE+WHEN+@i+<=+@jOffset+THEN+1+ELSE+@i+-+@jOffset+END
++++++++SET+@jEnd+=+CASE+WHEN+@i+%2B+@max+>=+@tLen+THEN+@tLen+ELSE+@i+%2B+@max+END

++++++++WHILE+(@j+<=+@jEnd)+BEGIN
++++++++++++--+at+this+point,+@distance+holds+the+previous+value+(the+cell+above+if+we+were+using+an+m+by+n+matrix)
++++++++++++SET+@left+=+UNICODE(SUBSTRING(@v0,+@j,+1))
++++++++++++SET+@thisJ+=+@j

++++++++++++SET+@distance+=+
++++++++++++++++CASE+WHEN+(@sChar+=+SUBSTRING(@t,+@j,+1))+THEN+@diag++++++++++++++++++++--match,+no+change
+++++++++++++++++++++ELSE+1+%2B+CASE+WHEN+@diag+<+@left+AND+@diag+<+@distance+THEN+@diag++++--substitution
+++++++++++++++++++++++++++++++++++WHEN+@left+<+@distance+THEN+@left++++++++++++++++++++--+insertion
+++++++++++++++++++++++++++++++++++ELSE+@distance++++++++++++++++++++++++++++++++++++++++--+deletion
+++++++++++++++++++++++++++++++++++END
+++++++++++++++++++++END
++++++++++++SET+@v0+=+STUFF(@v0,+@thisJ,+1,+NCHAR(@distance))
++++++++++++SET+@diag+=+@left
++++++++++++SET+@j+=+case+when+(@distance+>+@max)+AND+(@thisJ+=+@i+%2B+@lenDiff)+
++++++++++++++++then+@jEnd+%2B+2+
++++++++++++++++else+@thisJ+%2B+1+end
++++++++END
++++++++SET+@i+=+CASE+WHEN+@j+>+@jEnd+%2B+1+THEN+@sLen+%2B+1+ELSE+@i+%2B+1+END
++++END
++++RETURN+CASE+WHEN+@distance+<=+@max+THEN+@distance+ELSE+NULL+END
END|code-block|syntax|javascript|1409279|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

My mods for Azure Synapse (changed to use SET instead of SELECT):
<pre><code>-- =============================================
-- Computes and returns the Levenshtein edit distance between two strings, i.e. the
-- number of insertion, deletion, and sustitution edits required to transform one
-- string to the other, or NULL if @max is exceeded. Comparisons use the case-
-- sensitivity configured in SQL Server (case-insensitive by default).
-- 
-- Based on Sten Hjelmqvist's &quot;Fast, memory efficient&quot; algorithm, described
-- at http://www.codeproject.com/Articles/13525/Fast-memory-efficient-Levenshtein-algorithm,
-- with some additional optimizations.
-- =============================================
CREATE FUNCTION [db0].[Levenshtein](
 @s nvarchar(4000)
 , @t nvarchar(4000)
 , @max int
)
RETURNS int
WITH SCHEMABINDING
AS
BEGIN
 DECLARE @distance int = 0 -- return variable
 , @v0 nvarchar(4000)-- running scratchpad for storing computed distances
 , @start int = 1 -- index (1 based) of first non-matching character between the two string
 , @i int, @j int -- loop counters: i for s string and j for t string
 , @diag int -- distance in cell diagonally above and left if we were using an m by n matrix
 , @left int -- distance in cell to the left if we were using an m by n matrix
 , @sChar nchar -- character at index i from s string
 , @thisJ int -- temporary storage of @j to allow SELECT combining
 , @jOffset int -- offset used to calculate starting value for j loop
 , @jEnd int -- ending value for j loop (stopping point for processing a column)
 -- get input string lengths including any trailing spaces (which SQL Server would otherwise ignore)
 , @sLen int = datalength(@s) / datalength(left(left(@s, 1) + '.', 1)) -- length of smaller string
 , @tLen int = datalength(@t) / datalength(left(left(@t, 1) + '.', 1)) -- length of larger string
 , @lenDiff int -- difference in length between the two strings
 -- if strings of different lengths, ensure shorter string is in s. This can result in a little
 -- faster speed by spending more time spinning just the inner loop during the main processing.
 IF (@sLen &gt; @tLen) BEGIN
 SET @v0 = @s
 SET @i = @sLen -- temporarily use v0 for swap
 SET @s = @t
 SET @sLen = @tLen
 SET @t = @v0
 SET @tLen = @i
 END
 SET @max = ISNULL(@max, @tLen)
 SET @lenDiff = @tLen - @sLen

 IF @lenDiff &gt; @max RETURN NULL

 -- suffix common to both strings can be ignored
 WHILE(@sLen &gt; 0 AND SUBSTRING(@s, @sLen, 1) = SUBSTRING(@t, @tLen, 1))
 SET @sLen = @sLen - 1
 SET @tLen = @tLen - 1

 IF (@sLen = 0) RETURN @tLen

 -- prefix common to both strings can be ignored
 WHILE (@start &lt; @sLen AND SUBSTRING(@s, @start, 1) = SUBSTRING(@t, @start, 1)) 
 SET @start = @start + 1
 IF (@start &gt; 1) BEGIN
 SET @sLen = @sLen - (@start - 1)
 SET @tLen = @tLen - (@start - 1)

 -- if all of shorter string matches prefix and/or suffix of longer string, then
 -- edit distance is just the delete of additional characters present in longer string
 IF (@sLen &lt;= 0) RETURN @tLen

 SET @s = SUBSTRING(@s, @start, @sLen)
 SET @t = SUBSTRING(@t, @start, @tLen)
 END

 -- initialize v0 array of distances
 SET @v0 = ''
 SET @j = 1

 WHILE (@j &lt;= @tLen) BEGIN
 SET @v0 = @v0 + NCHAR(CASE WHEN @j &gt; @max THEN @max ELSE @j END)
 SET @j = @j + 1
 END

 SET @jOffset = @max - @lenDiff
 SET @i = 1

 WHILE (@i &lt;= @sLen) BEGIN
 SET @distance = @i
 SET @diag = @i - 1
 SET @sChar = SUBSTRING(@s, @i, 1)
 -- no need to look beyond window of upper left diagonal (@i) + @max cells
 -- and the lower right diagonal (@i - @lenDiff) - @max cells
 SET @j = CASE WHEN @i &lt;= @jOffset THEN 1 ELSE @i - @jOffset END
 SET @jEnd = CASE WHEN @i + @max &gt;= @tLen THEN @tLen ELSE @i + @max END

 WHILE (@j &lt;= @jEnd) BEGIN
 -- at this point, @distance holds the previous value (the cell above if we were using an m by n matrix)
 SET @left = UNICODE(SUBSTRING(@v0, @j, 1))
 SET @thisJ = @j

 SET @distance = 
 CASE WHEN (@sChar = SUBSTRING(@t, @j, 1)) THEN @diag --match, no change
 ELSE 1 + CASE WHEN @diag &lt; @left AND @diag &lt; @distance THEN @diag --substitution
 WHEN @left &lt; @distance THEN @left -- insertion
 ELSE @distance -- deletion
 END
 END
 SET @v0 = STUFF(@v0, @thisJ, 1, NCHAR(@distance))
 SET @diag = @left
 SET @j = case when (@distance &gt; @max) AND (@thisJ = @i + @lenDiff) 
 then @jEnd + 2 
 else @thisJ + 1 end
 END
 SET @i = CASE WHEN @j &gt; @jEnd + 1 THEN @sLen + 1 ELSE @i + 1 END
 END
 RETURN CASE WHEN @distance &lt;= @max THEN @distance ELSE NULL END
END
</code></pre>

问T-SQL中的Levenshtein距离
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问T-SQL中的Levenshtein距离EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问T-SQL中的Levenshtein距离
EN