Excluding records using regex-CodePudding

I'm trying to get exclude the email_id that has any name and end with either @abcd.in or @abcd.live and only include the email's having mobile numbers, but not sure if this is the correct regex I'm using, can you help?

the statement I'm using to filter is below

(NOT(lower(`table`.`user_email`) like '[a-z].*@Abcd.in$'|'[a-z].*@Abcd.live$')

CodePudding user response：

If you want to do filtering based on a regular expression, you should be using REGEXP or REGEXP_LIKE (both are synonyms). Assuming you just want to exclude the two domains mentioned, you could use:

SELECT *
FROM yourTable
WHERE email NOT REGEXP '[a-z] @Abcd\.(in|live)$';

Assuming you wanted to enhance the above by also whitelisting certain email patterns, you could make another call to REGEXP.

CodePudding user response：

I'll probably do something like this:

SELECT *
 FROM mytable
  WHERE SUBSTRING_INDEX(user_email,'@',-1) IN ('Abcd.live','Abcd.in')
   AND SUBSTRING_INDEX(user_email,'@',1) REGEXP '[0-9]'

Using SUBSTRING_INDEX() to separate the email name and domain by using @ as delimiter. The first condition is simply just filtering the domain with IN so other than the ones being defined, it will be omitted. Then the second condition is using REGEXP to check if numerical values are present in the email name.

Demo fiddle