I have tried several regex patterns (designed for use with PHP because I couldn't find any for MySQL) for URL validation, but none of them are working. Probably MySQL has a slightly different syntax.
I've also tried to come up with one, but no success.
So does anyone know a fairly good regex to use with MySQL for URL validation?
According to article 11.5.2. Regular Expressions in MySQL's documentation, you can perform selections with a regular expression with the following syntax
SELECT field FROM table WHERE field REGEX pattern
In order to match simple URLS, you may use
SELECT field FROM table
WHERE field REGEXP "^(https?://|www\\.)[\.A-Za-z0-9\-]+\\.[a-zA-Z]{2,4}"
This will match most urls like
But not
http:// or www optional? I want it to also match example.com or subdomain.example.com. Would that be easy?
http://about.museum would not be matched as valid, but www.foo.bar, with spaces! would pass as valid.
Although the answer KBA posted works, there are some inconstancies with the escaping.
The proper syntax should be, this syntax works in MySQL as well as in PHP for example.
SELECT field FROM table
WHERE field REGEXP "^(https?:\/\/|www\.)[\.A-Za-z0-9\-]+\.[a-zA-Z]{2,4}"
The above code will only match if the content of 'field' starts with a URL. If you would like to match all rows where the field contains a url (so for example surrounded by other text / content) just simply use:
SELECT field FROM table
WHERE field REGEXP "(https?:\/\/|www\.)[\.A-Za-z0-9\-]+\.[a-zA-Z]{2,4}"
SELECTall rows which don't match the URL pattern. (So all invalid URLs)