Oracle SQL Reg Exp check email

Question

I want to check if an email address fits a pattern:

-Only letters, numbers, and '.' or '_' symbols.

-The last part (ex: .com) must contain between 2 and 4 letters.

This is my Reg Exp: '[a-zA-Z0-9._]+@[a-zA-Z0-9._]+.[a-zA-Z]{2,4}'

The problem is that it accepts symbols like %, and .commmm is accepted as the last part. How could I solve it?

Wiktor Stribiżew · Accepted Answer · 2015-11-17 13:22:02Z

2

The main problems are actually two here:

You are using an unescaped . outside the character class that may match any symbol (but a newline)
You are not using anchors ^ and $, and thus you may match substring inside a larger string.

Use

'^[a-zA-Z0-9._]+@[a-zA-Z0-9._]+[.][a-zA-Z]{2,4}$'
 ^                             ^^^             ^

When you place a . into a pair of square brackets, you match a literal period.

answered Nov 17, 2015 at 13:22

Wiktor Stribiżew

631k41 gold badges502 silver badges632 bronze badges

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

Comments

Gordon Linoff · Accepted Answer · 2015-11-17 13:19:50Z

0

I think you just need ^ and $ to specify the beginning and end of the string:

 '^[a-zA-Z0-9.]+@[a-zA-Z0-9.]+.[a-zA-Z]{2,4}$'

You might want to slightly adjust the rules so the email and domain cannot start with a period:

 '^\w[a-zA-Z0-9.]*@\w[a-zA-Z0-9.]*.[a-zA-Z]{2,4}$'

answered Nov 17, 2015 at 13:19

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges