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

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

The parsename function isn't present in Oracle and I'm not sure how to write a regexp for this. Any开发者_如何学C help would be greatly appreciated!

Regex is overkill for this. All you need is

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

Edited with correction from the comments.

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.

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/

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 '@'