Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 261
  • Last Modified:

mysql query

I have a whole heap of spam in a table that I'm trying to get rid of.

What makes the posts the same is the following text:

[url=%url%][/url]

Open in new window


However when I search for it with the below query I get 0 results back which I assume is because of those % wildcard characters. How can I get around this to show all comments with the above text within them? Please ignore the /url tag because EE is asking me to put that in.

SELECT *
FROM FeaturesComments
WHERE COMMENT LIKE  ''
and ApprovalStatus = 0
0
PeterErhard
Asked:
PeterErhard
1 Solution
 
clockwatcherCommented:
\% should escape the % wildcard.  But if you're not getting any results with the wildcard in there, I can't see how escaping it would make something show up.  Escaping the wildcard is going to make it even more restrictive, so if nothing showed up before, I can't imagine you'd get anything with it escaped.  Ignore the spaces before/after the brackets-- needed them to get around the stupid EE parser.


SELECT *
FROM FeaturesComments
WHERE COMMENT LIKE   '[ url=\%url\% ][ /url]'
and ApprovalStatus = 0


But really think there is probably something else going on.
0
 
Dave BaldwinFixer of ProblemsCommented:
I'm confused.  Why don't you show us an actual entry?  Put it in the code section.
0
 
DOSLoverCommented:
To escape the % sign we need \%. In addition, we need a wild card % to use the like statement.
For example, in your search string, we need to escape the embedded % sign and then use actual % for wildcard, please try this

SELECT * 
FROM FeaturesComments
WHERE COMMENT LIKE  '%[url=\%url\%][ /url]%'
and ApprovalStatus = 0

Open in new window

0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
Ray PaseurCommented:
Please show us the actual data.  You can find this with phpMyAdmin.  You can copy it and paste it into the code snippet here at EE.  This will help us get you a better answer.
0
 
PeterErhardAuthor Commented:
Apologies for the late response.

An attached sample comment is attached.

The below query doesn't find it:

SELECT * 
FROM FeaturesComments
WHERE COMMENT LIKE  '%[url=\%url\%][ /url]%'
and ApprovalStatus = 0

Open in new window

0
 
PeterErhardAuthor Commented:
Forgot to attach.
spam.txt
0
 
clockwatcherCommented:
Try
like '[url=\%url\%]%[/url]'

Open in new window

0
 
PeterErhardAuthor Commented:
Thanks clockwatcher, but your query doesn't find it.
0
 
clockwatcherCommented:
Duh... sorry... forgot it had stuff before it... too early in the morning.]

like '%[url=\%url\%]%[/url]'

Open in new window

0
 
PeterErhardAuthor Commented:
That did the trick, thank you very much :)
0
 
clockwatcherCommented:
And that assumes that it really ends with  
[/url]

Open in new window


Can't really tell from your file.  If not then...

like '%[url=\%url\%]%[/url]%'

Open in new window

0
 
PeterErhardAuthor Commented:
Apologies for the late accept.
0

Featured Post

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now