Search MySQL database tables for URLs

Question

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"

Check this question: https://stackoverflow.com/questions/18317183/1139-got-error-repetition-operator-operand-invalid-from-regexp — Soheil Rahsaz, Aug 29 '21 at 17:09
You do not need that big pattern to check if there is a URL, try something like `'(http|ftp)s?://'` — Wiktor Stribiżew, Aug 29 '21 at 18:16
@WiktorStribiżew but when a text has something like this `No john don't use http:// use https:// instead` it doesn't contain a URL but its going to be selected... Please can u show me a proper way of making sure the selected text has a URL? — KANAYO AUGUSTIN UG, Aug 29 '21 at 19:15
I think it is still something like `'(http|ftp)s?://[^/[:space:]]+\\.[[:alpha:]]+'` — Wiktor Stribiżew, Aug 29 '21 at 19:19
`SHOW VARIABLES LIKE 'ver%';` -- There is no MySQL version 7. Maybe 5.7? — Rick James, Aug 30 '21 at 17:20

score 1 · Accepted Answer · answered Aug 29 '21 at 19:27

You can use

REGEXP '(http|ftp)s?://[^/[:space:]]+\\.[[:alpha:]]+'

The pattern means:

score -1 · Answer 2 · answered Aug 30 '21 at 04:50

-1

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_]

answered Aug 30 '21 at 04:50

Rick James

2 Answers2