CODEWITHSUNDEEP
mysqlregex
deacs
deacs

Reputation: 4319

Regex not working with mysql's REGEXP

Problem:

I want to get all records that contain a subdomain.

Some subdomains are saved prefixed with www. after the http://, but not all are.

Examples:

http://www.sub.domain.com and http://sub.domain.com

I have this working regex that I have tested on RegExr:

^(http:\/\/)(www\.)?(\w)+(\.)(\w)+(.)(\w|\/){2,10}

Which matches both examples nicely.

However when I try using this regex in my query using REGEXP, mysql returns 0 records.

I have tried:

SELECT * FROM `front` WHERE `domain` REGEXP '^(http:\/\/)(www\.)?(\w)+(\.)(\w)+(\.)(\w|\/){2,10}$';

SELECT * FROM `front` WHERE `domain` REGEXP '/^(http:\/\/)(www\.)?(\w)+(\.)(\w)+(\.)(\w|\/){2,10}$/';

SELECT * FROM `front` WHERE `domain` REGEXP '/^(http:\/\/)(www\.)?(\w)+(\.)(\w)+(\.)(\w|\/){2,10}$/g';

Which all return 0 records.

TL;DR

My working REGEX does not seem to be working when used in MySQL's REGEXP function.

Upvotes: 1

Views: 1383

Answers (2)

Gergo Erdosi
Gergo Erdosi

Reputation: 42073

There is no \w metacharacter support in MySQL. Use [A-Za-z0-9_] instead:

SELECT * FROM `front` WHERE `domain` REGEXP '^(http:\/\/)(www\.)?([A-Za-z0-9_])+(\.)([A-Za-z0-9_])+(.)([A-Za-z0-9_]|\/){2,10}$';

Upvotes: 4

Kendall Frey
Kendall Frey

Reputation: 44374

It's right there in the documentation:

Because MySQL uses the C escape syntax in strings (for example, “\n” to represent the newline character), you must double any “\” that you use in your REGEXP strings.

Upvotes: 2

Related Questions