使用正则表达式在SQL查询中分隔指定的值

Question

What I need is to get all the assigned values in a SQL queries. I am only interested in strings and numbers. For example:

SELECT * FROM my_table WHERE ((name="alex" AND age >= 24) OR gender =1) 
AND date = CURDATE() ORDER BY active = 1 LIMIT 1

By using PHP's preg_match I would like to get an array containing the following values:

$values = array(
    'alex'// name , 
    24    //age, 
    1     //gender,
    1     //active
);

I am a beginner with regular expressions and all I have is this:

preg_match_all('/\=\s*?(.*)\s*/', $sqlquery, $matches);

which will return a single match, the one after the first operator found

Answer 1

Something like

/[a-zA-Z0-9]+\s*[><!]?=\s*(["a-zA-Z0-9]+)(?=\)|\s|$)/

• [a-zA-Z0-9]+ Matches the left side of the equals to

• \s* Matches zero or more spaces.

• [><!]? Character class, matches > or < or !

  • ? Quantifier, ensures that the preceding character class occures zero or one time.

• = Matches the space.

• (["a-zA-Z0-9]+) Matches the right hand side of equals.

• (?=\)|\s|$) Positive look ahead. Checks if the right hand side of = is followed by \s or ) or end of string $

Test

$string = 'SELECT * FROM my_table WHERE ((name="alex" AND age >= 24) OR gender =1) 
AND date = CURDATE() ORDER BY active = 1 LIMIT 1';

preg_match_all('/[a-zA-Z0-9]+\s*[><!]?=\s*(["a-zA-Z0-9]+)(?=\)|\s|$)/', $string, $matches);
print_r($matches[1]);
// Outputs
// Array ( [0] => "alex" 
//         [1] => 24 
//         [2] => 1 
//         [3] => 1 )