[原创]sql like模式转换成等价的正则表达式的辅助类

sql like模式转换成等价的正则表达式的辅助类

%表示任意多个任意字符, _一个任意字符

这个类的用处是将匹配的like模式转换成正则表达式以便在脚本中可以实现高亮匹配通过like模式从数据库查询的结果的部分(preg_replace)

select ..... where col LIKE '%'.$likewhat.'%'

<?php class Parser { // convert a sql-like string('%'.$likewhat.'%') to an equal regular expression // i.e. `SELECT col FROM tbn WHERE col LIKE "%{$likewhat}%"` // equals `SELECT col FROM tbn WHERE col mathes Regex({Parser::SqlLikeToRegex($likewhat)})` (NOT real sql code) public static function SqlLikeToRegex($likewhat) { $likewhat = preg_replace('/%%+/', '%%', $likewhat); // strip duplicated % $pattern = ''; $end_1 = strlen($likewhat) - 1; $escape_characters = '^${}[].-+*?/():'; // escape the regex charset itsself $convert_characters = '%_'; for($i = 0; $i < $end_1; ++$i) { $c = $likewhat[$i]; if ($c == '//') { // check if it is escaping delimeter $next_c = $likewhat[ ++$i ]; if (strpos($escape_characters, $next_c) !== false) { // /[ => ///[, ... $pattern .= '//////'.$next_c; } else if (strpos($convert_characters, $next_c) !== false) { // /% => %, /_ => _ $pattern .= $next_c; } else if ($next_c == '//') { // // => // $pattern .= '////'; } else { // /w => //w, ... $pattern .= '////'.$next_c; } } else if (strpos($escape_characters, $c) !== false) { // [ => /[, ... $pattern .= '//'.$c; } else if ($c == '%') { // % => (?:.|[/n])*? //$pattern .= '(?:.|[/n])*'; // in this case, make return value '/('.$pattern.')/i', the same as below $pattern .= '.*'; } else if ($c == '_') { // _ => (?:.|[/n]) //$pattern .= '(?:.|[/n])'; // 非捕获模式(以提高效率), 若使用/s选项(.表示包括换行符的任意字符)会使模式简洁的多 $pattern .= '.'; } else { // S => S $pattern .= $c; } } if ($i == $end_1) { // notice here $likewhat[$i-1] is NOT '//'! $c = $likewhat[$i]; if ($c == '//') { // / => % (final $likewhat is '%'.$likewhat.'%', when $c == '//', % has become himself $pattern .= '%'; } else if (strpos($escape_characters, $c) !== false) { // /[ => ///[, ... $pattern .= '//'.$c; } else if ($c == '_') { //$pattern .= '(?:.|[/n])'; $pattern .= '.'; } else if ($c != '%') { // strip the end % because of the final format '%'.$likewhat.'%' $pattern .= $c; } } //echo '/((?:'.$pattern.')+)/is'; return '/((?:'.$pattern.')+)/is'; // /s explains meta character '.' matching any character(including '/n') } public static function IsEmptyPattern($pattern) { return $pattern == self::SqlLikeToRegex(''); } } ?>