CODEWITHSUNDEEP
phpmysqlregex
Reham Fahmy
Reham Fahmy

Reputation: 5063

applying preg_match on mysql

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.

Upvotes: 3

Views: 17570

Answers (5)

alex347
alex347

Reputation: 631

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

Upvotes: 6

quickshiftin
quickshiftin

Reputation: 69621

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 :)

Upvotes: 1

Sorin Buturugeanu
Sorin Buturugeanu

Reputation: 1840

I'm not aware of any REGEX for MySQL (but I'm not saying there aren't, I'm just not aware of them). But for what you're describing, you could try:

delete from table where field like '%something.com%'

Upvotes: -1

SergeS
SergeS

Reputation: 11779

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

Upvotes: 1

Djumaka
Djumaka

Reputation: 510

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 :)

Upvotes: 3

Related Questions