Кто сталкивался знает, что SoundEx в MSSQL 2000 не заточен для Cyrillic ...
Покопался тут в просторах и-нета и нарыл кое-что ...
Прошу прощения у автора за небольшую модификацию кода ...
Собственно то, что многие хотели бы увидеть ...
@maxdist — максимальное различие между @p и @t.
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ufStrCompareEx]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[ufStrCompareEx]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE FUNCTION dbo.ufStrCompareEx (@p varchar(255), @t varchar(255), @maxdist tinyint)
RETURNS integer AS
BEGIN
DECLARE @dist tinyint
DECLARE @d varchar(255)
DECLARE @dp varchar(255)
DECLARE @dt varchar(255)
DECLARE @plen tinyint
DECLARE @tlen tinyint
SELECT @plen = LEN(@p)
SELECT @tlen = LEN(@t)
DECLARE @i tinyint
DECLARE @j tinyint
SELECT @i = 1
SELECT @d = ''
WHILE @i<=@plen
BEGIN
SELECT @d = @d + CHAR(@i)
SELECT @i = @i + 1
END
DECLARE @dst1 tinyint
DECLARE @dst2 tinyint
DECLARE @dst3 tinyint
DECLARE @dst4 tinyint
DECLARE @ct varchar(1)
DECLARE @ctp varchar(1)
SELECT @ctp=''
SELECT @j = 1
WHILE @j<=@tlen
BEGIN
SELECT @ct = substring(@t,@j,1)
SELECT @i = 1
SELECT @dt = NULL
WHILE @i<=@plen
BEGIN
IF @i = 1
SELECT @dst1 = @j-1
ELSE
SELECT @dst1 = ASCII(substring(@d,@i-1,1))
IF @ct <> substring(@p,@i,1)
SELECT @dst1 = @dst1 + 1
SELECT @dst2 = ASCII(substring(@d,@i,1))+1
IF @i = 1
SELECT @dst3 = @j +1
ELSE
SELECT @dst3 = ASCII(substring(@dt,@i-1,1)) + 1
SELECT @dst4 = @dst1
IF @j > 1 and @i > 1
IF @ctp = substring(@p,@i,1) and @ct = substring(@p,@i-1,1)
BEGIN
IF @i = 2
SELECT @dst4 = @j - 2 + 1
ELSE
SELECT @dst4 = ASCII(substring(@dp,@i-2,1)) + 1
END
DECLARE @dst tinyint
SELECT @dst = @dst1
IF @dst2 < @dst
SELECT @dst = @dst2
IF @dst3 < @dst
SELECT @dst = @dst3
IF @dst4 < @dst
SELECT @dst = @dst4
IF @i >1
SELECT @dt = @dt + CHAR(@dst)
ELSE
SELECT @dt = CHAR(@dst)
SELECT @i = @i + 1
END
SELECT @ctp = @ct
SELECT @dp = @d
SELECT @d = @dt
SELECT @j = @j + 1
END
SELECT @dist = ASCII(substring(@d,@plen,1))
IF @dist > @maxdist + 1
SELECT @dist = @maxdist + 1
RETURN @dist
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[COMPARE_EX]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[COMPARE_EX]
GO
CREATE TABLE [dbo].[COMPARE_EX] (
[Str] [varchar] (255) COLLATE Cyrillic_General_CI_AS NOT NULL
) ON [PRIMARY]
GO
INSERT INTO COMPARE_EX
VALUES('саша')
INSERT INTO COMPARE_EX
VALUES('СаШа')
INSERT INTO COMPARE_EX
VALUES('леша')
INSERT INTO COMPARE_EX
VALUES('лёШа')
INSERT INTO COMPARE_EX
VALUES('суша')
INSERT INTO COMPARE_EX
VALUES('шуша')
INSERT INTO COMPARE_EX
VALUES('Маша')
INSERT INTO COMPARE_EX
VALUES('Даша')
INSERT INTO COMPARE_EX
VALUES('Каша')
SELECT Str, dbo.ufStrCompareEx(Str, 'чаша', 4) FROM COMPARE_EX