CODEWITHSUNDEEP
sqlregexoracle-database
v8rs
v8rs

Reputation: 307

Oracle SQL Reg Exp check email

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?

Upvotes: 1

Views: 636

Answers (2)

Wiktor Stribiżew
Wiktor Stribiżew

Reputation: 626845

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.

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269793

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

Upvotes: 0

Related Questions