Why Does My MySQL REGEXP Query Fail with \'repetition-operator operand invalid\'?

Failed Regular Expression: 'repetition-operator operand invalid'

When attempting to execute the following MySQL query:

SELECT text 
FROM `articles` 
WHERE content REGEXP '.*<img . order by date desc alt="Why Does My MySQL REGEXP Query Fail with \'repetition-operator operand invalid\'?" ><p>you encounter the error: #1139 - Got error 'repetition-operator operand invalid' from regexp. Despite the regular expression functioning correctly in Notepad  , MySQL rejects it.</p>
<h3 id="Understanding-the-Issue-POSIX-vs-PCRE">Understanding the Issue: POSIX vs. PCRE</h3>
<p>The MySQL regular expression engine adheres to POSIX 1003.2, which lacks support for the question mark (?) as a non-greedy modifier in quantifiers (<em> and  ). Consequently, you cannot employ </em>? and  ? in your regular expressions.</p>
<h3 id="Resolution-Utilizing-a-Greedy-Quantifier">Resolution: Utilizing a Greedy Quantifier</h3>
<p>To resolve this issue, employ the greedy version of the quantifier, which will still suffice for your intended purpose. Nevertheless, to prevent undesired matching of elements like:</p>
<pre class="brush:php;toolbar:false"><img  alt="Why Does My MySQL REGEXP Query Fail with \'repetition-operator operand invalid\'?" >

incorporate a negated character class as follows:

'<img  alt="Why Does My MySQL REGEXP Query Fail with \'repetition-operator operand invalid\'?" >]*src="http://www'

Remember that the " character does not necessitate escaping and that the .* at the beginning is implicit.

The above is the detailed content of Why Does My MySQL REGEXP Query Fail with \'repetition-operator operand invalid\'?. For more information, please follow other related articles on the PHP Chinese website!