Regex not working with mysql's REGEXP

Question

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.

What I usually do in these situations is try to gradually build up the regex. Start out with a simple one that matches a superset of the rows you need and then slowly add more parts of it. You will eventually figure out which part is causing it to fail — Ioan Alexandru Cucu
– Ioan Alexandru Cucu, Commented Jan 7, 2015 at 14:07
Well the problem is that the regex works! It just doesn't work when putting it through mysql's REGEXP function. I will update my answer to be clearer about this. — deacs
– deacs, Commented Jan 7, 2015 at 14:08
The fact that it works on regexr.com is not very relevant. MySQL might not support the same set of regex features that the website does. Try building it in MySQL from scratch. — Ioan Alexandru Cucu
– Ioan Alexandru Cucu, Commented Jan 7, 2015 at 14:10

Gergo Erdosi · Accepted Answer · 2015-01-07 14:14:19Z

4

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}$';

answered Jan 7, 2015 at 14:14

Gergo Erdosi

42.3k21 gold badges122 silver badges95 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

deacs Over a year ago

This was it! I atleast got records back now, looks like I still need to tweak the REGEX abit though. Thanks for your help!

Kendall Frey · Accepted Answer · 2015-01-07 14:14:01Z

2

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.

answered Jan 7, 2015 at 14:14

Kendall Frey

44.6k21 gold badges113 silver badges151 bronze badges

Collectives™ on Stack Overflow

Regex not working with mysql's REGEXP

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related