Team Leader
4Nov2009,14:12   #11
pradeep's Avatar
They are similar but not the same, in your case as it seems you are looking for people having PHP skills, you should use the 1st query!
Newbie Member
4Nov2009,17:12   #12
scvinodkumar's Avatar
Thanks pradeep but I have checked the execution time for two queries,1st query takes much time than 2nd query.
Team Leader
4Nov2009,22:44   #13
pradeep's Avatar
Yes the regexp operator consumes more resources than a like!
Newbie Member
8Nov2011,16:47   #14
noky's Avatar
Hi!

I have a table with one text field. This field can have urls I need get only de urls but no the rest of the field. I have gotten this with a query first and then a php function but I think will be more efficient get the urls with only one query. Also, If I use this solution I get repeated urls and I should create other function to delete that then is quite unefficient... My database is so big, around 3.5gb then I need be very efficient...

For example:

Quote:
1. field: "This is my web 'url1' testing..."
2. field: "I don't have url"
3. field: "Testing.... 'url2' testing..."
4. field: "Test----- 'url1' test..."

I need get only "url1" and "url2"
This is my solution with php:


PHP Code:
$sql = " SELECT field_text FROM  table";

function researchUrl ($text_field){

    $pattern = '`.*?((http|ftp)://[\w#$&+,\/:;=?@.-]+)[^\w#$&+,\/:;=?@.-]*?`i';
    if (preg_match_all($pattern,$text_field,$matches)) {
        return $matches[1];
    }else{
        return 0;
    }
} 

But with this solution I get "url1", "url2" and "url1"

I would use a regular expression in a query to obtain them directly .. Something like that:


PHP Code:
SELECT text_field
FROM table
WHERE text_field
REGEXP  '`.*?((http|ftp)://[\w#$&+,\/:;=?@.-]+)[^\w#$&+,\/:;=?@.-]*?`i' 

But with this query mysql return this error: "Got error 'repetition-operator operand invalid' from regexp" and I don't know why...

I hope you can help me... Thank you!

Regards!
Page 2 of 2
1
2