Solved

mysql query

Posted on 2013-06-01
12
257 Views
Last Modified: 2013-06-14
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
Comment
Question by:PeterErhard
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
12 Comments
 
LVL 25

Expert Comment

by:clockwatcher
ID: 39213640
\% 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
 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 39213786
I'm confused.  Why don't you show us an actual entry?  Put it in the code section.
0
 
LVL 5

Expert Comment

by:DOSLover
ID: 39213845
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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
LVL 110

Expert Comment

by:Ray Paseur
ID: 39215086
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
 

Author Comment

by:PeterErhard
ID: 39215490
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
 

Author Comment

by:PeterErhard
ID: 39215491
Forgot to attach.
spam.txt
0
 
LVL 25

Expert Comment

by:clockwatcher
ID: 39215503
Try
like '[url=\%url\%]%[/url]'

Open in new window

0
 

Author Comment

by:PeterErhard
ID: 39215504
Thanks clockwatcher, but your query doesn't find it.
0
 
LVL 25

Expert Comment

by:clockwatcher
ID: 39215519
Duh... sorry... forgot it had stuff before it... too early in the morning.]

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

Open in new window

0
 

Author Comment

by:PeterErhard
ID: 39215523
That did the trick, thank you very much :)
0
 
LVL 25

Accepted Solution

by:
clockwatcher earned 500 total points
ID: 39215524
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
 

Author Closing Comment

by:PeterErhard
ID: 39249501
Apologies for the late accept.
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
insert row field data graphically 4 40
Generate PDF from MySQL using PHP 3 71
How to extract database info from current month and year 7 45
Unwanted output from my query 5 53
This guide whil teach how to setup live replication (database mirroring) on 2 servers for backup or other purposes. In our example situation we have this network schema (see atachment). We need to replicate EVERY executed SQL query on server 1 to…
Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question