PHP regular expression details how to extract characters between two strings (supports single & multiple)

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];

Tags: SQL hive

Posted on Wed, 27 May 2020 10:49:36 -0400 by ts2000abc