Go4Expert

Go4Expert (http://www.go4expert.com/)
-   MySQL (http://www.go4expert.com/articles/mysql/)
-   -   Regular Expressions in MySQL (http://www.go4expert.com/articles/regular-expressions-mysql-t2337/)

pradeep 4Nov2009 14:12

Re: Regular Expressions in MySQL
 
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!

scvinodkumar 4Nov2009 17:12

Re: Regular Expressions in MySQL
 
Thanks pradeep but I have checked the execution time for two queries,1st query takes much time than 2nd query.

pradeep 4Nov2009 22:44

Re: Regular Expressions in MySQL
 
Yes the regexp operator consumes more resources than a like!

noky 8Nov2011 16:47

Re: Regular Expressions in MySQL
 
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!


All times are GMT +5.5. The time now is 01:37.