applying preg_match on mysql

Question

If db_table with set of urls

1,www.something.any/djjjj
2,www.anything.any/nsmms
3,www.google.com

etc

and i want filter some certain url of exact prefix which is something.any with help i've got answer say this

if (preg_match('/something.com/i', $url) > 0) {
    // Your error message here
}

The question

How to apply same idea of preg_match to delete all of it from db_table

$sql= "delete from urls where preg_match('/something.com/i', $url) > 0";
mysql_query($sql) or die("query failed: $sql".mysql_error()); 

it gives indeed error but that is the exact idea i want to apply so any idea !

as i'm gonna makes connection to db then call all urls in my table then apply it to delete all urls added with that exact prefix something.com and that is it.

Solution 1

DELETE FROM urls WHERE url REGEXP 'something\.com'

Solution 2

Consider reading this... http://dev.mysql.com/doc/refman/5.1/en/regexp.html.

There is a simpler method "LIKE Statement" read here http://www.tutorialspoint.com/mysql/mysql-like-clause.htm. The second is usefull in simpler cases, where you want to select "all strings starting with *". But it does an exact match If any other quesions, please ask :)

Solution 3

Eh ... did you know about % modidfier in strings in MySQL - eg.

delete from urls where url like '%something.com%'

it is NOT regexp, but it will work too and is much faster

Solution 4

There are several tools in MySQL that are similar, 2 of which have been mentioned already

• LIKE queries
• POSIX regular expressions

If you want something more powerful you can install the PREG plugin for MySQL.

I'd recommend trying to solve it with one of the firs 2 solutions first though as the PREG plugin can be a bit of a hassle to get running and likely won't be in your production environment unless you're in tight w/ the DBA's :)

View more solutions

Comments

[quickshiftin]

You're not aware of REGEX for MySQL? Did you read @Djumaka's post before putting your answer?

[Sorin Buturugeanu]

We were writing our answers at the same time. Still, this has nothing to do with the fact that the solution I provided is perfectly valid.

[quickshiftin]

Just saying I would probly drop a quick google for something rather than say "I'm not aware of..", looks like my vote is 'locked in now' (by SO), sorry bro.