I am trying to search a table for text column that contains a URL. So I used this:
SELECT * FROM table WHERE text REGEXP '((http|ftp|https):\/\/([\w_-]+(?:(?:\.[\w_-]+)+))([\w.,@?^=%&:\/~+#-]*[\w@?^=%&\/~+#-])?)'
But I get this error below
#1139 - Got error 'repetition-operator operand invalid' from regexp
I need to get rows that the text column contains a URL string, something like this:
"Hello http://www.google.com World http://examples.com"
You can use
REGEXP '(http|ftp)s?://[^/[:space:]]+\\.[[:alpha:]]+'
The pattern means:
(http|ftp) - either http or ftps? - an optional s:// - a :// string[^/[:space:]]+ - one or more chars other than / and whitespace\\. - a . (it is in fact \. text)[[:alpha:]]+ - one or more letters.When including a dash in a character class, beware. It has special meaning.
[\w_-]
-->
[-\w_]
Dash is not special in the first position, so it will work as just another character.
If you are using 8.0, double up any backslashes.
[-\\w_]