regex - Regexp to validate URL in MySQL

Question

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?

Answer 1

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

• www.google.il
• http://google.com/
• http://ww.google.net/
• www.google.com/index.php?test=data
• https://yahoo.dk/as
• http://goo.gle.com/
• http://wt.a.x24-s.org/ye/
• www.website.info

But not

• htp://google.com
• ww.google.com/
• www-google.com
• http://google.c
• http://goo#.com
• httpf://google.com