Search HTML using LIKE Operator in Database MySQL

Question

I have stored some html in my database, for example:

ID | Data
1  | <a href=\"link\" class=\"someclass\" id=\"id_10923074\"><h3 class=\"class1 class2\"><\/h3><br \/><div class=\"clearfix\"><\/div><\/a>
2  | <a href=\"lin2\" class=\"someclass\" id=\"id_10923075\"><h3 class=\"class1 class2\">some text<\/h3><br \/><div class=\"clearfix\"><\/div><\/a>

Now, I would like to query an invalid records which doesn't contain text in the h3, which is row 1.

I have tried many queries, some are bellow:

SELECT `mytable`.* FROM `mytable` WHERE (Data LIKE '%<h3 class=\"class1 class2\"><\/h3>%')

SELECT `mytable`.* FROM `mytable` WHERE (Data LIKE '%h3 class="class1 class2"></h3%')

SELECT `mytable`.* FROM `mytable` WHERE (Data LIKE '"%class1 class2%"')

SELECT `mytable`.* FROM `mytable` WHERE (Data LIKE '%<h3 class=\"class1 class2\">%')

What am I missing here? I have been checking many questions here but cannot find any solution.

Thank you.

Is it simply not returning anything, or is it actually doing a sql error? — IncredibleHat, Jan 25 '18 at 15:51
Its simply not returning any thing, it says zero rows found. — mubashermubi, Jan 25 '18 at 15:55

score 1 · Answer 1 · answered Jan 25 '18 at 15:51

1

Your code is working as expected. See this sqlfiddle:

SELECT * FROM `mytable` WHERE (Data LIKE '%<h3 class=\"class1 class2\"><\/h3>%')

Your issue most likely lies with your quoting. Ensure you are escaping backslashes (\) and quotes (") properly in your PHP code.

answered Jan 25 '18 at 15:51

Blue

22,608
7
62
92

Thanks, I am trying direcly running in mysql as well, for example: SELECT `mytable`.* FROM `mytable` WHERE (Data LIKE '%
%') doesn't work in my mysql directly, but in your sqlfiddle it works, any ideas?
– mubashermubi Jan 25 '18 at 16:01
Does your data contain physical backlashes in the database? If yes, then ensure you're passing `\\\"` to match for both a backslash, and a quote. – Blue Jan 25 '18 at 16:04
Yes, data originally looks like how I showed in question. the slashes are physical in database. I have tried \\\" but no success :( – mubashermubi Jan 25 '18 at 16:11
There is no php code, I am just querying using mysql directly now so I can delete the invalid records. – mubashermubi Jan 26 '18 at 07:13

score 1 · Accepted Answer · answered Jan 26 '18 at 07:37

Found the solution here:

So the trick is to double escape ONLY the backslash, for string escapes only a single escape is needed.

For example

The single quote ' only needs escaping once LIKE '%\'%'
But to query backslash \ you need to double escape to LIKE '%\\\\%'
If you wanted to query backslash+singlequote \' then LIKE '%\\\\\'%' (with 5 backslashes)

Explanation Source excerpt:

Because MySQL uses C escape syntax in strings (for example, “\n” to represent a newline character), you must double any “\” that you use in LIKE strings. For example, to search for “\n”, specify it as “\n”. To search for “\”, specify it as “\”; this is because the backslashes are stripped once by the parser and again when the pattern match is made, leaving a single backslash to be matched against.

Correct Query:

SELECT * FROM `mytable` WHERE (Data LIKE '%<h3 class=\\\\"class1 class2\\\\"><\\\\/h3>%')

score 0 · Answer 3 · answered Jan 25 '18 at 18:36

0

To find rows where Data has at least one <h3> (with or without any attributes such as class=) with no text before </h3>:

WHERE Data REGEXP '<h3[^>]*></h3>'

answered Jan 25 '18 at 18:36

Rick James

135,179
13
127
222

[No](https://stackoverflow.com/questions/1732348/regex-match-open-tags-except-xhtml-self-contained-tags/1732454#1732454) – Blue Jan 25 '18 at 18:47
@FrankerZ - "No" meaning what? Fails to find empty h3s? Or fails to ignore empty h3s? Give an example. – Rick James Jan 25 '18 at 18:59
Thanks for help, but it doesn't work either, returns zero rows. – mubashermubi Jan 26 '18 at 07:15
@M3Dev - Strange. I changed FrankerZ's sqlfiddle to use my REGEXP, and I got one row. – Rick James Jan 26 '18 at 18:29

Search HTML using LIKE Operator in Database MySQL

3 Answers3

%') doesn't work in my mysql directly, but in your sqlfiddle it works, any ideas?