MySql regexp on column with html

Question

I have a table with HTML content in which I need to select all rows containing a span element with the CSS class "glyphicon glyphicon-film" with some text content, but exclude all rows without content or just containing a white space.

These rows should NOT match:

<span class="glyphicon glyphicon-film">&nbsp;</span>
<span class="glyphicon glyphicon-film"></span>
<span class="glyphicon glyphicon-film"> </span>
<span class="hypo-link-target" data-link-target-id="1379">Hammartå</span>

These rows should match:

<span class="glyphicon glyphicon-film">nån text</span>
<span class="glyphicon glyphicon-film">Nån Text</span>
<span class="glyphicon glyphicon-film">NÅN TEXT</span>
<span class="glyphicon glyphicon-film">&nbsp;nån text</span>
<span class="glyphicon glyphicon-film">&nbsp;Nån Text</span>
<span class="glyphicon glyphicon-film">&nbsp; nån text</span>
<span class="glyphicon glyphicon-film">&nbsp; Nån Text</span>
<span class="glyphicon glyphicon-film"> nån text</span>
<span class="glyphicon glyphicon-film"> Nån Text</span>

The query I have at the moment:

select * from Section 
where (Text regexp '(span class="glyphicon glyphicon-film">&nbsp;.+</span>)' 
or Text regexp 'span class="glyphicon glyphicon-film">[a-zA-Z][|å|ä|ö|Å|Ä|Ö]+</span>');

Which regexp should I use to get the desired result?

UPDATE: Based on suggestions in the answer I ended up with this query which I think works for all cases. This query will also match on rows with two or more span elements where some elements are correct and some are not, like this one:

<span class="glyphicon glyphicon-film">&nbsp;Nån Text</span>more content here... and a correct span <span class="glyphicon glyphicon-film">&nbsp;</span>

select * from Section 
where Text regexp 'span class="glyphicon glyphicon-film">&nbsp;.+</span>' 
        or (Text regexp 'span class="glyphicon glyphicon-film">[a-zA-ZåäöÅÄÖ ]+</span>'
and Text not regexp 'span class="glyphicon glyphicon-film"> </span>');

Barmar · Accepted Answer · 2020-09-22 13:40:22Z

1

The pattern [a-zA-Z][|å|ä|ö|Å|Ä|Ö] matches a string beginning with an ASCII letter, followed by any mixture of |åäöÅÄÖ. You want to match a string of any combination of these characters, as well as spaces, which you left out. That would be [a-zA-ZåäöÅÄÖ ]+.

| is not used inside [] to specify alternatives, since [] is already specifying alternative characters.

select * from Section 
where (Text regexp 'span class="glyphicon glyphicon-film">&nbsp;.+</span>' 
        or Text regexp 'span class="glyphicon glyphicon-film">[a-zA-ZåäöÅÄÖ ]+</span>')
and Text not regexp 'span class="glyphicon glyphicon-film"> +</span>';

The last condition is needed to prevent <span class="glyphicon glyphicon-film"> </span> from matching.

DEMO

edited Sep 22, 2020 at 13:40

answered Sep 21, 2020 at 18:33

Barmar

789k57 gold badges555 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

poa Over a year ago

Thanks for the tip but it didn't work. With your change I only got match on the rows containing " xxx", nothing else.

Rick James Over a year ago

"space" is still missing from the regexp.

Barmar Over a year ago

@poa I've updated the answer and added a demo showing it works.

poa Over a year ago

@Barmar ahh yes this was a lot better. With a slight modification I think it works fine now. I updated the question with the query I think is good. Thanks.

Collectives™ on Stack Overflow

MySql regexp on column with html

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related