Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
 SELECT FIRSTNAME,LASTNAME, EMAIL
   FROM CUSTOMERS
   WHERE REGEXP_LIKE(EMAIL,'[A-Z0-9._%-]{1,10}@[a-z0-9._%-]{1,5}\.[net|com]','i');

I know this is not practical or make a ton of sense, but its a constraint I'm working with: email address with upto 10 characters prior to @ followed by upto 5 characters, then a '.' and finally the .net or .com extension. This works for the most part except the 1-10 constraint on the first half does not appear to do what I'd like. It pulls in pretty much any number characters prior to the '@'. For example it should accept jreed but not jodywilliamreed as the later is more than 10 characters. Anyone see what I've done here that would cause this problem? edit: I'm using sql developer with oracle

share|improve this question
    
Are you sure this is for SQL Server? I don't believe it has a REGEXP_LIKE function. I know Oracle has this function. docs.oracle.com/cd/B14117_01/server.101/b10759/… –  Garett Feb 23 '12 at 5:47
    
shoot sorry, my bad, SQL DEveloper, so yes its Oracle. –  jreed72 Feb 23 '12 at 15:40
add comment

1 Answer 1

up vote 3 down vote accepted

Try adding the ^ character to your regex, which means to match the position at the beginning of the string, like this

^[A-Z0-9._%-]{1,10}@[a-z0-9._%-]{1,5}\.[net|com]
share|improve this answer
    
I had tried that, but was using it comjunction with the $ at the end of the string. This wasn't working for me. However, just the carat^ at the beginning does solve the problem! Thank you Garett. –  jreed72 Feb 23 '12 at 15:49
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.