Regular expression problem

Question

 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

• Garett: 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/…
• jreed72: shoot sorry, my bad, SQL DEveloper, so yes its Oracle.

Answer 1

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]

• jreed72: 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.