Поиск различных вариантов транслитерации русских текстов (ФИО) латиницей для выборок в MySQL

Собственно, столкнулся с банальной задачей — есть база, в которой есть масса данных ФИО, написанных латиницей. Зачастую достаточно безграмотно и непредсказуемо транслитерировано. Вроде бы и есть ГОСТ 7.79 (ISO 9), но на практике его далеко не всегда соблюдают.
Задача — есть БД с записями (как правило, ФИО) латиницей. Плохой латиницей. Требуется по русской подстроке поиска в вэб-приложении выбрать из БД (MySQL) записи, похоже транслитерированные на латиницу.

Первая мысль — написать функцию, которая возвращала бы массив, содержащий возможные написания исходной фразы латиницей.

print_r (r2e ("Очень простая фраза на русском языке"));

function r2e ($txt)
 {
  $r=array(mb_strtolower($txt,"windows-1251"));
  $r=r2es($r,"/кс/i",array("x","ks","cs"));
  $r=r2es($r,"/ей/i",array("ey","ei"));
  $r=r2es($r,"/ов/i",array("ov","off"));
  $r=r2es($r,"/а/i",array("a"));
  $r=r2es($r,"/б/i",array("b"));
  $r=r2es($r,"/в/i",array("v","w"));
  $r=r2es($r,"/г/i",array("g"));
  $r=r2es($r,"/д/i",array("d"));
  $r=r2es($r,"/е/i",array("e"));
  $r=r2es($r,"/ё/i",array("yo","jo"));
  $r=r2es($r,"/ж/i",array("zh","z"));
  $r=r2es($r,"/з/i",array("z","s"));
  $r=r2es($r,"/и/i",array("i"));
  $r=r2es($r,"/й/i",array("j","y"));
  $r=r2es($r,"/к/i",array("k","c"));
  $r=r2es($r,"/л/i",array("l"));
  $r=r2es($r,"/м/i",array("m"));
  $r=r2es($r,"/н/i",array("n"));
  $r=r2es($r,"/о/i",array("o"));
  $r=r2es($r,"/п/i",array("p"));
  $r=r2es($r,"/р/i",array("r"));
  $r=r2es($r,"/с/i",array("s"));
  $r=r2es($r,"/т/i",array("t"));
  $r=r2es($r,"/у/i",array("u"));
  $r=r2es($r,"/ф/i",array("f"));
  $r=r2es($r,"/х/i",array("h","kh"));
  $r=r2es($r,"/ц/i",array("c","ts"));
  $r=r2es($r,"/ч/i",array("ch"));
  $r=r2es($r,"/ш/i",array("sh"));
  $r=r2es($r,"/щ/i",array("shch","sch","sh"));
  $r=r2es($r,"/ъ/i",array(""));
  $r=r2es($r,"/ы/i",array("y"));
  $r=r2es($r,"/ь/i",array(""));
  $r=r2es($r,"/э/i",array("e"));
  $r=r2es($r,"/ю/i",array("u","yu","ju"));
  $r=r2es($r,"/я/i",array("ya","ja"));
  return $r;
 }

function r2es ($var, $pattern, $splits)
 {
  $sp=array(); $nsp=array();
  foreach ($var as $v)
    if (preg_match($pattern,$v)) foreach ($splits as $split) $sp=array_merge($sp,array(preg_replace($pattern,$split,$v)));
    else $nsp=array_merge($nsp,array($v));
  return array_merge($sp,$nsp);
 }

вроде и хорошо получилось, даже познавательно, но полученный массив нужно ещё и склеить в довесок к запросу в виде

$sqlstr = "SELECT <smth> FROM <smwhr> WHERE searchField LIKE '%" . implode("%' OR searchField LIKE '%",$result) . "%'";
либо, если нужно точное совпадение
$sqlstr = "SELECT <smth> FROM <smwhr> WHERE searchField LIKE '" . implode("' OR searchField LIKE '",$result) . "'";

Ну и технически настораживает, что запрос SQL совершенно легко может удлинниться в разы.
Зато с минимальными доработками будет работать практически с любым движком БД.

Решение задачи оказалось значительно компактнее — MySQL поддерживает вместо «SELECT… WHERE field LIKE 'pattern'» продвинутые конструкции «SELECT… WHERE field REGEXP 'regexp'», позволяющие выполнять поиск по regexp'ам.

Итого — компактный код со значительно лучшими показателями при работе с MySQL

function r2e ($txt)
 {
  $txt=mb_strtolower($txt,"windows-1251");
  $sr=array("кс","ей","ов","а","б","в","г","д","е","ё","ж","з","и","й","к","л","м","н","о","п","р","с","т","у","ф","х","ц","ч","ш","щ","ъ","ы","ь","э","ю","я");
  $se=array("(x|[kc]s)","e[yi]","o(v|ff)","a","b","[vw]","g","d","e","[yj]o","z(h)*","[zs]","i","[jy]","[kc]","l","m","n","o","p","r","s","t","u","f","(k|c)*h","(c|ts)","ch","sh","s(h)*(c)*h","","y","","e","[yj]*u","[yj]a");
  return str_replace($sr,$se,$txt);
 }

в результате возвращается строка вида

ochen prosta[yj]a fra[zs]a na russ[kc]om [yj]a[zs]y[kc]e

вполне пригодная для вставки в «SELECT columns FROM table WHERE searchField REGEXP '$txt'» в качестве, собственно, $txt.

А что с другими популярными СУБД?
— К сожалению, с MSSQL все оказалось не столь радужно — хотя можно использовать CLR-модуль (например, отсюда) и получить поддержку регулярных выражений.
— С T-SQL (Oracle) все хорошо, по крайней мере с 10G версии есть REGEXP_LIKE(fieldName, regexpString).
— в PostgreSQL есть достаточно давно regexp_matches(fieldName, regexpString).
— в FireBird можно использовать SIMILAR TO, при этом придется переработать синтаксис, например для славно русской «Щ» вместо s(h)*(с)*h нужно будет описать как s(h){0,1}©{0,1}h и далее по аналогии.

Автор: xx3l

Источник