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

Question

I need to count the number of email addresses in db that have 3 or less characters before the @ sign, e.g. [e-mail].

The parseame function is missing in Oracle, and I'm not sure how to write regexp for this. Any help would be greatly appreciated!

Answer 1

Regex is superfluous for this. All you need is

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

Edited to correct comments.

Answer 2

You want a regex:

^[^@]{0,3}@

In English, this is:

• Start line
• 0 , .
• .

Answer 3

WHERE COUNT REGEXP_COUNT:

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

COUNT:

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

:

• Oracle
• http://www.regular-expressions.info/

Answer 4

instr (t.email, '@') < 5

, t.email "@"! , t.email "@"