CODEWITHSUNDEEP
mysqlregexpattern-matching
Eduard Luca
Eduard Luca

Reputation: 6611

Regexp to validate URL in MySQL

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?

Upvotes: 3

Views: 13907

Answers (2)

Ludo - Off the record
Ludo - Off the record

Reputation: 5533

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}"

Upvotes: 1

kba
kba

Reputation: 19466

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

Upvotes: 9

Related Questions