How to count numbers in an email address

bjain7

I am trying to identify email address with more than 4 numbers occurring before @. I tried the following code

CASE
WHEN LENGTH(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
LEFT(Email, INSTR(Email, '@') - 1),
'a', ''), 'b', ''), 'c', ''), 'd', ''), 'e', ''),
'f', ''), 'g', ''), 'h', ''), 'i', ''), 'j', ''),
'k', ''), 'l', ''), 'm', ''), 'n', ''), 'o', ''),
'p', ''), 'q', ''), 'r', ''), 's', ''), 't', ''),
'u', ''), 'v', ''), 'w', ''), 'x', ''), 'y', ''), 'z', '')
) > 4
THEN 'More than 4 numbers before @'
ELSE '4 or fewer numbers before @'
END

but i keep getting syntax errors. Any thoughts on easier ways to do this? Appreciate all the help and thanks in advance.

ColemenWilson

Yeah! The best thing to do is split the email out so you only have characters left of the @ sign. Then remove all text and only keep numbers. Then count the characters left.

Use the following:
CHAR_LENGTH(REGEXP_REPLACE(SUBSTRING_INDEX(Email, '@', 1),'[^0-9]+',''))

Then use a case statement to filter out anything you don't want to count:

CASE WHEN `Left of @` < 4 THEN 'Less than 4' ELSE '4 or more' END

ColemenWilson

Yeah! The best thing to do is split the email out so you only have characters left of the @ sign. Then remove all text and only keep numbers. Then count the characters left.

Use the following:
CHAR_LENGTH(REGEXP_REPLACE(SUBSTRING_INDEX(Email, '@', 1),'[^0-9]+',''))

Then use a case statement to filter out anything you don't want to count:

CASE WHEN `Left of @` < 4 THEN 'Less than 4' ELSE '4 or more' END

bjain7

That worked. Thanks Colemen.