Опытный
Профиль
Группа: Участник
Сообщений: 838
Регистрация: 11.6.2005
Где: Москва ДАС-МГУ
Репутация: нет
Всего: 17
|
| Код |
DELIMITER ;;;
CREATE FUNCTION METAPHONE (W VARCHAR(400) ) RETURNS VARCHAR(400)
BEGIN
DECLARE alf varchar(400);
DECLARE cns1 varchar(400);
DECLARE cns2 varchar(400);
DECLARE cns3 varchar(400);
DECLARE ch varchar(400);
DECLARE ct varchar(400);
DECLARE S varchar(400);
DECLARE V varchar(400);
DECLARE i int;
DECLARE B int;
DECLARE c char(1);
DECLARE old_c char(1);
DECLARE tmps varchar(40);
SET @alf = CONVERT(_cp1251'ОЕАИУЭЮЯПСТРКЛМНБВГДЖЗЙФХЦЧШЩЁЫ' USING utf8);
SET @cns1 = CONVERT(_cp1251'БЗДВГ' USING utf8);
SET @cns2 = CONVERT(_cp1251'ПСТФК' USING utf8);
SET @cns3 = CONVERT(_cp1251'ПСТКБВГДЖЗФХЦЧШЩ' USING utf8);
SET @ch = CONVERT(_cp1251'ОЮЕЭЯЁЫ' USING utf8);
SET @ct = CONVERT(_cp1251'АУИИАИА' USING utf8);
SET @S = '';
SET @V = '';
SET @i = 1;
--- return(CONVERT(@alf ));
--- return(CONVERT(CONCAT(@alf,CONVERT(W USING cp1251)) using utf8));
--- return (LOCATE(SUBSTRING(UPPER(W), @i, 1),@alf));
WHILE @i <= CHAR_LENGTH(W) DO
SET @c = SUBSTRING(UPPER(W), @i, 1);
IF LOCATE(@c, @alf)>0 THEN
SET @S = CONCAT(@S, @c);
END IF;
SET @i=@i+1;
END WHILE;
IF CHAR_LENGTH(@S) = 0 THEN RETURN ''; END IF;
IF CHAR_LENGTH(@S)>6 THEN
CASE RIGHT(@S, 6)
WHEN CONVERT(_cp1251'ОВСКИЙ' USING utf8) THEN SET @tmps='@';
WHEN CONVERT(_cp1251'ЕВСКИЙ' USING utf8) THEN SET @tmps='#';
WHEN CONVERT(_cp1251'ОВСКАЯ' USING utf8) THEN SET @tmps='$';
WHEN CONVERT(_cp1251'ЕВСКАЯ' USING utf8) THEN SET @tmps='%';
ELSE SET @tmps=RIGHT(@S, 6);
END CASE;
--- return(CHAR_LENGTH(@S));
SET @S = CONCAT(LEFT(@S, CHAR_LENGTH(@S) - 6), @tmps);
END IF;
--- return(@S);
IF CHAR_LENGTH(@S)>4 THEN
CASE RIGHT(@S, 4)
WHEN CONVERT(_cp1251'ИЕВА' USING utf8) THEN SET @tmps='9';
WHEN CONVERT(_cp1251'ЕЕВА' USING utf8) THEN SET @tmps='9';
ELSE SET @tmps=RIGHT(@S, 4);
END CASE;
SET @S = CONCAT(LEFT(@S, CHAR_LENGTH(@S) - 4), @tmps);
END IF;
IF CHAR_LENGTH(@S)>3 THEN
CASE RIGHT(@S, 3)
WHEN CONVERT(_cp1251'ОВА' USING utf8) THEN SET @tmps='9';
WHEN CONVERT(_cp1251'ЕВА' USING utf8) THEN SET @tmps='9';
WHEN CONVERT(_cp1251'ИНА' USING utf8) THEN SET @tmps='1';
WHEN CONVERT(_cp1251'ИЕВ' USING utf8) THEN SET @tmps='4';
WHEN CONVERT(_cp1251'ЕЕВ' USING utf8) THEN SET @tmps='4';
WHEN CONVERT(_cp1251'НКО' USING utf8) THEN SET @tmps='3';
ELSE SET @tmps=RIGHT(@S, 3);
END CASE;
SET @S = CONCAT(LEFT(@S, CHAR_LENGTH(@S) - 3), @tmps);
END IF;
IF CHAR_LENGTH(@S)>2 THEN
CASE RIGHT(@S, 2)
WHEN CONVERT(_cp1251'ОВ' USING utf8) THEN SET @tmps='4';
WHEN CONVERT(_cp1251'ЕВ' USING utf8) THEN SET @tmps='4';
WHEN CONVERT(_cp1251'АЯ' USING utf8) THEN SET @tmps='6';
WHEN CONVERT(_cp1251'ИЙ' USING utf8) THEN SET @tmps='7';
WHEN CONVERT(_cp1251'ЫЙ' USING utf8) THEN SET @tmps='7';
WHEN CONVERT(_cp1251'ЫХ' USING utf8) THEN SET @tmps='5';
WHEN CONVERT(_cp1251'ИХ' USING utf8) THEN SET @tmps='5';
WHEN CONVERT(_cp1251'ИН' USING utf8) THEN SET @tmps='8';
WHEN CONVERT(_cp1251'ИК' USING utf8) THEN SET @tmps='2';
WHEN CONVERT(_cp1251'ЕК' USING utf8) THEN SET @tmps='2';
WHEN CONVERT(_cp1251'УК' USING utf8) THEN SET @tmps='0';
WHEN CONVERT(_cp1251'ЮК' USING utf8) THEN SET @tmps='0';
ELSE SET @tmps=RIGHT(@S, 2);
END CASE;
SET @S = CONCAT(LEFT(@S, CHAR_LENGTH(@S) - 2), @tmps);
END IF;
SET @B = LOCATE(RIGHT(@S, 1), @cns1);
IF @B > 0 THEN
SET @S = CONCAT(LEFT(@S, CHAR_LENGTH(@S)-1), SUBSTRING(@cns2, @B, 1));
END IF;
SET @old_c = ' ';
SET @i = 1;
WHILE @i <= CHAR_LENGTH(@S) DO
SET @c = SUBSTRING(@S, @i, 1);
SET @B = LOCATE(@c, @ch);
IF @B > 0 THEN
IF @old_c = CONVERT(_cp1251'Й' USING utf8) OR @old_c = CONVERT(_cp1251'И' USING utf8) THEN
IF @c = CONVERT(_cp1251'О' USING utf8) OR @c = CONVERT(_cp1251'Е' USING utf8) THEN
SET @old_c = CONVERT(_cp1251'И' USING utf8);
SET @S = CONCAT(LEFT(@S, CHAR_LENGTH(@S)-1), @old_c);
ELSE
IF @c <> @old_c THEN SET @V = CONCAT(@V, SUBSTRING(@ct, @B, 1)); END IF;
END IF;
ELSE
IF @c <> @old_c THEN SET @V = CONCAT(@V, SUBSTRING(@ct, @B, 1)); END IF;
END IF;
ELSE
IF @c <> @old_c AND LOCATE(@c, @cns3)>0 THEN
SET @B = LOCATE(@old_c, @cns1);
IF @B>0 THEN
SET @old_c = SUBSTRING(@cns2, @B, 1);
SET @V = CONCAT(LEFT(@V, CHAR_LENGTH(@V)-1), @old_c);
END IF;
END IF;
IF @c <> @old_c THEN SET @V = CONCAT(@V, @c); END IF;
END IF;
SET @old_c = @c;
SET @i = @i + 1;
END WHILE;
RETURN (@V);
END
;;;
|
|
METAPHONE - SOUNDEX-подобный алгоритм для русских слов, перевёл алгоритм на mysql 
Загрузка ...
