Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

Simple table domains: int id, varchar name

table must contain valid domains (we'll consider that domain is valid if it's in form .*\.[\w\d_-]{2,3}

We don't need to validate against DNS of course. Running that SQL:

SELECT * FROM domain WHERE name NOT REGEXP '.*\.[\w\d_-]{2,3}'

returns me some unpredictable result (domains which to me absolutely meets the condition)

example:

95323   vandtech.dk
95324   vanee.demon.co.uk
95325   vanens.com
95326   vanger.nl
95327   vangstein.no
95328   vanhoorn-kiel.de
95329   vanityeventi.com
95330   vanloosen.de

but does not return invalid entries such as:

1.396.041   _.
1.396.042   _.75
1.396.043   _.ca
1.396.044   _23.com

Could someone explan me what I'm doing wrong with regexp?

share|improve this question
add comment

1 Answer

It looks like you're using a Perl-style regex, but MySQL doesn't understand those. You can replace, for example, \d with [:digit:]. Check out §11.4.2 of the MySQL 5.1 Reference Manual.

I'm not 100% sure, but I think your regex would look like:

.*\.[[:alnum:][:digit:]_-]{2,3}

though to be honest, I think there is some overlap between the various classes. Maybe it could be represented more simply:

.*\.[[:alnum:]_-]{2,3}

I should point out, too, that the underscore _ is not valid in any part of a domain name.

share|improve this answer
    
>>>I should point out, too, that the underscore _ is not valid in any part of a domain name. Thanks for that remark, you're indeed right. –  archer Feb 27 '10 at 5:58
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.