0
pradeep's Avatar, Join Date: Apr 2005
Team Leader
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!
0
scvinodkumar's Avatar, Join Date: Nov 2009
Newbie Member
Thanks pradeep but I have checked the execution time for two queries,1st query takes much time than 2nd query.
0
pradeep's Avatar, Join Date: Apr 2005
Team Leader
Yes the regexp operator consumes more resources than a like!
0
noky's Avatar, Join Date: Nov 2011
Newbie Member
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!