How to count numbers in an email address

bjain7
bjain7 Member
in Beast Mode

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.

Best Answer

  • ColemenWilson
    ColemenWilson Coach
    edited February 26 Answer ✓

    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]+',''))

    Screenshot 2024-02-26 at 1.18.54 PM.png

    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

    If I solved your problem, please select "yes" above

Answers

  • ColemenWilson
    ColemenWilson Coach
    edited February 26 Answer ✓

    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]+',''))

    Screenshot 2024-02-26 at 1.18.54 PM.png

    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

    If I solved your problem, please select "yes" above

  • bjain7
    bjain7 Member

    That worked. Thanks Colemen.

Categories