preface:
Sometimes it is necessary to extract some characters from a long string, which is often used for variable query and replacement.
Scenario:
If I have a hive SQL statement, I need to replace the variable {@ date} with a specific date, such as 20200526,
select "F" as sex, c.moviename as name, avg(a.rate) as avgrate, count(c.moviename) as total from t_rating a join t_user b on a.userid = b.userid join t_movie c on a.movieid = c.movieid where b.sex = "F" and a.dt = {@date} and b.dt = {@date } group by c.moviename having total >= 50 order by avgrate desc limit 10;
Solution:
As mentioned above, the format of {@ date} written by different users in SQL is different. There are some spaces in the user's writing method, such as {@ date}. There are many situations. If we list them by this way, it is very tedious and the code execution efficiency is very low, then how to solve them? At this time, the optimal solution should be to use strong regular matching to solve such problems;
resolvent:
Using the method of regular retrieval, the regular is:
$regex = '/\{\s*([\w\W]*?)\s*\}/'; preg_match_all($regex, $sql . " ", $matches); Regular interpretation: \s * means any blank character, where * is zero or more times of the previous sub expression matching, and is greedy mode *? non greedy mode, stop execution after encountering the following characters \w: matches any word character including underscores, equivalent to [A-Z a-z 0-9] \W: match any non word character, equivalent to [^ A-Z a-z 0-9 ']The above SQL{@date} variable replacement date method is as follows:
/** * @date Date variable string format * @param string $sql * @param bool $date * @return mixed */ public static function dateFormat($sql, $date = false) { //Regular take all contain@date String{} $regex = '/\{\s*([\w\W]*?)\s*\}/'; preg_match_all($regex, $sql . " ", $matches); if (!empty($matches[0])) { foreach ($matches[0] as $string) { if (stristr($string, '@date')) { //Remove string space $cleanString = str_replace(' ', '', $string); //If you pass it on $date If yes, the replacement variable is specific@date value if ($date) { if ($cleanString == '{@date}') { $cleanString = $date; } $sql = str_replace($string, $cleanString, $sql); } } } } return $sql; }
In addition, for simple single string extraction, you can use the following methods:
//Extract the string in {}
preg_match
('/{.*}/',$sql,$match); echo $match[0];