• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 278
  • Last Modified:

sql state ment to select the row that not is email address in mysql

I have a column email (varchar)
how can I select  rows that column "email" is not email
1 Solution
I would consider to do the check in frontend code.

But by SQL you can check some elements of "valid email"-rules, here I found example:

--No embedded spaces
- '@' can't be the first character of an email address
- '.' can't be the last character of an email address
- There must be a '.' somewhere after '@'
- the '@' sign is allowed
- Domain name should end with at least 2 character extension
- can't have patterns like '.@' and '..'

SELECT email_address WHERE NOT (
     CHARINDEX(' ',LTRIM(RTRIM(@email_address))) = 0 
AND  LEFT(LTRIM(@email_address),1) <> '@' 
AND  RIGHT(RTRIM(@email_address),1) <> '.' 
AND  CHARINDEX('.',@email_address ,CHARINDEX('@',@email_address)) - CHARINDEX('@',@email_address ) > 1 
AND  LEN(LTRIM(RTRIM(@email_address ))) - LEN(REPLACE(LTRIM(RTRIM(@email_address)),'@','')) = 1 
AND  CHARINDEX('.',REVERSE(LTRIM(RTRIM(@email_address)))) >= 3 
AND  (CHARINDEX('.@',@email_address ) = 0 AND CHARINDEX('..',@email_address ) = 0)

Open in new window

Jagadishwor DulalBraces MediaCommented:
Fetch your row and check the fetched record is valid email or not.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Train for your Pen Testing Engineer Certification

Enroll today in this bundle of courses to gain experience in the logistics of pen testing, Linux fundamentals, vulnerability assessments, detecting live systems, and more! This series, valued at $3,000, is free for Premium members, Team Accounts, and Qualified Experts.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now