Отправляет email-рассылки с помощью сервиса Sendsay

Вопросы и ответы по MS SQL Server

Подписаться Бесплатная новостная рассылка Подписчиков 17.253 RSS
←   Май 2003   →
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31


За последние 60 дней 1 выпусков (1 раз в 2 месяца)


Сайт рассылки: http://www.itnews.ru
Открыта: 04-03-2003

Автор
Сергей

Статистика

17.253 подписчиков
-3 за неделю
←   Все выпуски   →

Вопросы и ответы по MS SQL Server


Информационный Канал Subscribe.Ru

Вопросы и ответы по MS SQL Server

Выпуск No. 14 от 2003-05-23
Вопрос : Почему функция SOUNDEX не работает с русскими словами?
Ответ:
Функция SOUNDEX предназначена для работы только с английским шрифтом и английскими словами.
Это сразу становится понятно, если посмотреть исходный код функции, опубликованный в книге Кена Хендерсона "The Guru's Guide to Transact-SQL". А также становится очевидным тот факт, что транслитерация русских названий в латинские с последующим использованием SOUNDEX не приводит к желаемому результату. 
USE master

go

IF OBJECT_ID('sp_soundex') IS NOT NULL

  DROP PROC sp_soundex

go

CREATE PROCEDURE sp_soundex @instring varchar(50), @soundex varchar(50)=NULL OUTPUT

/*

Object: sp_soundex Description: Returns the soundex of a string

Usage: sp_soundex @instring=string to translate, @soundex OUTPUT=string in which to return soundex

Returns: (None)

Created by: Ken Henderson. Email: khen@khen.com

Version: 7.0

Example: sp_soundex "Rodgers"

Created: 1998-05-15. Last changed: 1998-05-16.

Notes: Based on the soundex algorithm published by Robert Russell and Margaret ODell in 1918.

Translation to Transact-SQL by Ken Henderson.

*/

AS

IF (@instring='/?') GOTO Help



DECLARE @workstr varchar(10)



SET @instring=UPPER(@instring)

SET @soundex=RIGHT(@instring,LEN(@instring)-1)-- Put all but the first char in a work buffer (we always return the first char)



SET @workstr='AEHIOUWY'-- Remove these from the string

WHILE (@workstr<>'') BEGIN

  SET @soundex=REPLACE(@soundex,LEFT(@workstr,1),'')

  SET @workstr=RIGHT(@workstr,LEN(@workstr)-1)

END



/*

Translate characters to numbers per the following table:



Char             Number

B,F,P,V          1

C,G,J,K,Q,S,X,Z  2

D,T              3

L                4

M,N              5

R                6

*/



SET @workstr='BFPV'

WHILE (@workstr<>'') BEGIN

  SET @soundex=REPLACE(@soundex,LEFT(@workstr,1),'1')

  SET @workstr=RIGHT(@workstr,LEN(@workstr)-1)

END



SET @workstr='CGJKQSXZ'

WHILE (@workstr<>'') BEGIN

  SET @soundex=REPLACE(@soundex,LEFT(@workstr,1),'2')

  SET @workstr=RIGHT(@workstr,LEN(@workstr)-1)

END



SET @workstr='DT'

WHILE (@workstr<>'') BEGIN

  SET @soundex=REPLACE(@soundex,LEFT(@workstr,1),'3')

  SET @workstr=RIGHT(@workstr,LEN(@workstr)-1)

END



SET @soundex=REPLACE(@soundex,'L','4')



SET @workstr='MN'

WHILE (@workstr<>'') BEGIN

  SET @soundex=REPLACE(@soundex,LEFT(@workstr,1),'5')

  SET @workstr=RIGHT(@workstr,LEN(@workstr)-1)

END



SET @soundex=REPLACE(@soundex,'R','6')



-- Now replace repeating digits (e.g., '11' or '22') with single digits

DECLARE @c int

SET @c=1

WHILE (@c<10) BEGIN

  SET @soundex=REPLACE(@soundex,CONVERT(char(2),@c*11),CONVERT(char(1),@c))-- Multiply by 11 to produce repeating digits

  SET @c=@c+1

END

SET @soundex=REPLACE(@soundex,'00','0')-- Get rid of double zeros



SET @soundex=LEFT(@soundex,3)

WHILE (LEN(@soundex)<3) SET @soundex=@soundex+'0'-- Pad with zero



SET @soundex=LEFT(@instring,1)+@soundex-- Prefix first char and return

RETURN 0



Help:

EXEC sp_usage @objectname='sp_soundex',

  @desc='Returns the soundex of a string',

  @parameters='@instring=string to translate, @soundex OUTPUT=string in which to return soundex',

  @author='Ken Henderson',

  @email='khen@khen.com',

  @datecreated='19980515',

  @datelastchanged='19980516',

  @version='7',

  @revision='0',

  @example='sp_soundex "Rodgers"'

RETURN -1

GO

Проверить соответствие этой процедуры и функции SOUNDEX можно с помощью следующего кода: 
DECLARE @mysx varchar(4)

EXEC sp_soundex 'Rogers',@mysx OUTPUT

SELECT @mysx,SOUNDEX('Rogers')

GO

Результат: 


---- -----

R262 R262



(1 row(s) affected)

Для английского языка кроме алгоритма SoundEx существует алгоритм MetaPhone. Реализация этого алгоритма для русского языка показал Каньковски Пётр (kankowski@narod.ru) в журнале "Программист" за август 2002 года. Вы можете воспользоваться функцией MetaPhoneRu, переведенной мной на Transact-SQL: 
IF exists (SELECT * from dbo.sysobjects where id = object_id(N'[dbo].[MetaPhoneRu]') and xtype in (N'FN', N'IF', N'TF'))

  drop function [dbo].[MetaPhoneRu]

GO

CREATE FUNCTION dbo.MetaPhoneRu (@W varchar(4000))

RETURNS varchar(4000)

AS

BEGIN

DECLARE @alf varchar(4000), @cns1 varchar(4000), @cns2 varchar(4000), @cns3 varchar(4000), @ch varchar(4000), @ct varchar(4000)



SET @alf = 'ОЕАИУЭЮЯПСТРКЛМНБВГДЖЗЙФХЦЧШЩЁЫ'

SET @cns1 = 'БЗДВГ'

SET @cns2 = 'ПСТФК'

SET @cns3 = 'ПСТКБВГДЖЗФХЦЧШЩ'

SET @ch = 'ОЮЕЭЯЁЫ'

SET @ct = 'АУИИАИА'

-- @alf - алфавит кроме исключаемых букв, @cns1 и @cns2 - звонкие и глухие

-- согласные, @cns3 - согласные, перед которыми звонкие оглушаются,

-- @ch, @ct - образец и замена гласных



DECLARE @S varchar(4000), @V varchar(4000), @i int, @B int, @c char(1), @old_c char(1)

-- @S, @V - промежуточные строки, @i - счётчик цикла,

-- @B - позиция найденного элемента, @c - текущий символ



SET @W = UPPER(@W)

SET @S = ''

SET @V = ''



SET @i = 1

WHILE @i <= LEN(@W)

BEGIN

  SET @c = SUBSTRING(@W, @i, 1)

  IF CHARINDEX(@c, @alf)>0 SET @S = @S + @c

  SET @i=@i+1

END



IF LEN(@S) = 0 RETURN ''



-- Заменяем окончания

IF LEN(@S)>6

SET @S = LEFT(@S, LEN(@S) - 6) +

CASE RIGHT(@S, 6)

  WHEN 'ОВСКИЙ' THEN '@'

  WHEN 'ЕВСКИЙ' THEN '#'

  WHEN 'ОВСКАЯ' THEN '$'

  WHEN 'ЕВСКАЯ' THEN '%'

  ELSE RIGHT(@S, 6)

END



IF LEN(@S)>4

SET @S = LEFT(@S, LEN(@S) - 4) +

CASE RIGHT(@S, 4)

  WHEN 'ИЕВА' THEN '9'

  WHEN 'ЕЕВА' THEN '9'

  ELSE RIGHT(@S, 4)

END



IF LEN(@S)>3

SET @S = LEFT(@S, LEN(@S) - 3) +

CASE RIGHT(@S, 3)

  WHEN 'ОВА' THEN '9'

  WHEN 'ЕВА' THEN '9'

  WHEN 'ИНА' THEN '1'

  WHEN 'ИЕВ' THEN '4'

  WHEN 'ЕЕВ' THEN '4'

  WHEN 'НКО' THEN '3'

  ELSE RIGHT(@S, 3)

END



IF LEN(@S)>2

SET @S = LEFT(@S, LEN(@S) - 2) +

CASE RIGHT(@S, 2)

  WHEN 'ОВ' THEN '4'

  WHEN 'ЕВ' THEN '4'

  WHEN 'АЯ' THEN '6'

  WHEN 'ИЙ' THEN '7'

  WHEN 'ЫЙ' THEN '7'

  WHEN 'ЫХ' THEN '5'

  WHEN 'ИХ' THEN '5'

  WHEN 'ИН' THEN '8'

  WHEN 'ИК' THEN '2'

  WHEN 'ЕК' THEN '2'

  WHEN 'УК' THEN '0'

  WHEN 'ЮК' THEN '0'

  ELSE RIGHT(@S, 2)

END



-- Оглушаем последний символ, если он - звонкий согласный:

SET @B = CHARINDEX(RIGHT(@S, 1), @cns1)

IF @B > 0

  SET @S = LEFT(@S, LEN(@S)-1) + SUBSTRING(@cns2, @B, 1)



SET @old_c = ' '

SET @i = 1

WHILE @i <= LEN(@S)

BEGIN

  SET @c = SUBSTRING(@S, @i, 1)

  SET @B = CHARINDEX(@c, @ch)

  IF @B > 0

  BEGIN

    IF @old_c = 'Й' OR @old_c = 'И'

    BEGIN

      IF @c = 'О' OR @c = 'Е'

      BEGIN

        SET @old_c = 'И'

        SET @S = LEFT(@S, LEN(@S)-1) + @old_c

      END

      ELSE

        IF @c <> @old_c SET @V = @V + SUBSTRING(@ct, @B, 1)

    END

    ELSE

    BEGIN

      IF @c <> @old_c SET @V = @V + SUBSTRING(@ct, @B, 1)

    END

  END

  ELSE

  BEGIN

    IF @c <> @old_c

      AND CHARINDEX(@c, @cns3)>0

    BEGIN

      SET @B = CHARINDEX(@old_c, @cns1)

      IF @B>0

      BEGIN

        SET @old_c = SUBSTRING(@cns2, @B, 1)

        SET @V = LEFT(@V, LEN(@V)-1) + @old_c

      END

    END

    IF @c <> @old_c SET @V = @V + @c

  END

  SET @old_c = @c

  SET @i = @i + 1

END



RETURN (@V)

END

GO

А вот и пример работы с этой функцией: 
SELECT dbo.MetaPhoneRu('Грицюк')

UNION ALL

SELECT dbo.MetaPhoneRu('Грицук')

UNION ALL

SELECT dbo.MetaPhoneRu('Грецук')

UNION ALL

SELECT dbo.MetaPhoneRu('Аввакумов')

UNION ALL

SELECT dbo.MetaPhoneRu('Авакумов')

UNION ALL

SELECT dbo.MetaPhoneRu('Авакуумов')

Результат: 


---------

ГРИЦ0

ГРИЦ0

ГРИЦ0

АВАКУМ4

АВАКУМ4

АВАКУМ4



(6 row(s) affected)

Совсем другой "коленкор". Только для сравнения строк, содержащих несколько слов (например, ФИО) процедуру необходимо доработать.

Еще для сравнения слов существует функция, авторство которой теряется в дебрях интернета в процессе переделки с одного языка программирования на другой. Принцип работы этой функции больше похож на принцип работы функции DIFFERENCE, но в отличие от нее выдает меньший коэффициент при меньшем расхождении слов: 
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[StrCompareEx]') and xtype in (N'FN', N'IF', N'TF'))

drop function [dbo].[StrCompareEx]

GO

CREATE FUNCTION dbo.StrCompareEx (@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

Рассмотрим пример работы с этой функцией, а также результат ее работы совместно с предыдущей функцией MetaPhone: 
CREATE TABLE #Table1 (Name varchar(200))



INSERT #Table1

SELECT 'Иевлев'

UNION ALL

SELECT 'Иванова'

UNION ALL

SELECT 'Петров'

UNION ALL

SELECT 'Сидоров'

UNION ALL

SELECT 'Илонов'

UNION ALL

SELECT 'Тифонов'

UNION ALL

SELECT 'Иванко'

UNION ALL

SELECT 'Данко'





SELECT Name,

 dbo.StrCompareEx(Name, 'Иванов', 20) AS Dist,

 dbo.StrCompareEx(dbo.MetaPhoneRu(Name), dbo.MetaPhoneRu('Иванов'), 20) AS DistEx

 FROM #Table1



DROP TABLE #Table1

GO

Результат: 
Name    Dist DistEx

------- ---- ------

Иевлев     4      3

Иванова    1      1

Петров     4      4

Сидоров    4      3

Илонов     2      1

Тифонов    3      2

Иванко     2      2

Данко      4      4



(8 row(s) affected)

Подведем итог.
Использование стандартной функции SOUNDEX возможно только с английскими словами. Для работы с русскими словами можно использовать приведенные выше функции или найти более совершенные алгоритмы сравнения.
Надеюсь, Вы нашли эту информацию полезной. Вопросы, предложения и пожелания шлите на адрес sql@likor.ru
С уважением, Сергей Кошкин.
Архив рассылки смотрите на сайте Рассылки http://sql.softmatics.ru/
Рейтинг@Mail.ru
http://subscribe.ru/
E-mail: ask@subscribe.ru 		Отписаться
Убрать рекламу
В избранное