Parse email address in Oracle to count number of addresses with 3 or less chars before @ sign

Question

I need to count number of email addresses in a db that have 3 or less characters before the @ sign, for example [email protected].

The parsename function isn't present in Oracle and I'm not sure how to write a regexp for this. Any help would be greatly appreciated!

Answer 1

The regular expression you want is:

^[^@]{0,3}@

In English, that's:

• Start of string
• between 0 and three things that aren't an at sign.
• an at sign.

Answer 2

You could define the WHERE clause & use COUNT, or skip to use REGEXP_COUNT instead:

SELECT REGEXP_COUNT(t.email, '^[^@]{0,3}@', 1, 'i') RESULT
  FROM TABLE t;

Using COUNT:

SELECT COUNT(*)
  FROM TABLE t
 WHERE REGEXP_LIKE(t.email, '^[^@]{0,3}@')

Reference:

• Oracle Regular Expression Syntax
• http://www.regular-expressions.info/

Answer 3

Regex is overkill for this. All you need is

instr(t.email, '@') < 5 AND instr(t.email, '@') > 0

Edited with correction from the comments.

Answer 4

There is a little problem with following instr(t.email, '@') < 5

this query will work provided t.email has a '@' ! other wise it will return those entries also where t.email is not having '@'