Email validation in SQL :

In my previous article, I have given the basic idea of regular expressions and its different functions. The main question is how these regular expressions work in real life scenarios. Email validation in SQL can be achieved with multiple ways. The best way is using the regular expressions user can validate the email. Before Oracle 11G the developer needs to write the function for Email validation in SQL.You can achieve the Email validation in SQL server also. Just like REGEXP function in Oracle, SQL server has PATINDEX function. So this article not only gives the idea about Email validation in oracle it will give the details about Email validation in SQL Server also.

Email Validation in Oracle :

The REGEXP function is written in oracle 11 G.Before that user needs to write function and then it needs to use in procedure or select statement.

Using Function:

Create or replace

FUNCTION xx_check_email(l_user_name IN VARCHAR2)

RETURN VARCHAR2 IS

l_dot_pos    NUMBER;

l_at_pos     NUMBER;

l_str_length NUMBER;

BEGIN

l_dot_pos    := instr(l_user_name

,’.’);

l_at_pos     := instr(l_user_name

,’@’);

l_str_length := length(l_user_name);

IF ((l_dot_pos = 0) OR (l_at_pos = 0) OR (l_dot_pos = l_at_pos + 1) OR

(l_at_pos = 1) OR (l_at_pos = l_str_length) OR

(l_dot_pos = l_str_length))

THEN

RETURN ‘FAILURE’;

END IF;

IF instr(substr(l_user_name

,l_at_pos)

,’.’) = 0

THEN

RETURN ‘FAILURE’;

END IF;

RETURN ‘SUCCESS’;

end xx_check_email;

 

The above function code is written for validating the email in Oracle. If the Email is valid then the specified function will return ‘SUCCESS’ else it will return ‘FAILURE’.

After writing this function check the function or test the function using simple select statement and use function in it.

Query:

Select xx_check_email(‘amiet@gmail.com’) from dual;

Output:

XX_CHECK_EMAIL(‘AMIET@GMAIL.COM’)                                                                            ——————————————–                                                                                                                                                                                                                           SUCCESS

Query:

Select xx_check_email(‘amietgmail.com’) from dual;

Output:

XX_CHECK_EMAIL(‘AMIET@GMAIL.COM’)

——————————————–                                                                                                FAILURE

This is the simple function used in oracle to validate e-mail address.

 

Using REGEXP_LIKE:

This is the simplest way of validating the email. If user is using the Oracle 11G and higher version of oracle then use simple regular expression function to validate the email address.There are some specific rules to validate the email in oracle.

1.First Part of email id :

The first part of email id is always the alphabet.So user needs to check whether it starts with alphabet.This condition will be added with ^[A-Za-z0-9.]. in regular expression.

2.Checking of @ Symbol :

The second part of email id is checking of @(at the rate) symbol.This condition will be achieved with @[A-Za-z0-9.-]. in regular expression.

3.Checking of dot (.) followed by alphabet :

The third part of email address is nothing but dot followed by alphabets. This condition will be achieved with \.[A-Za-z]{2,4}$. Here, \. Searches for the literal character DOT.

Query:

WITH T_validate AS

(SELECT ‘amiets@gmail.com’ email FROM dual)

SELECT * FROM T_validate WHERE REGEXP_LIKE (EMAIL, ‘^[A-Za-z]+[A-Za-z0-9.]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,4}$‘);

Output:

EMAIL

—————-

amiets@gmail.com

Email Validation in Microsoft SQL :

Using the function like REGEXP_LIKE user can validate the email in MSSQL also. User can write different function in ms sql to validate mail like in oracle.

Query:

SELECT email

FROM Employee

WHERE email LIKE ‘%_@__%.__%’

AND PATINDEX(‘%[^a-z,0-9,@,.,_]%’, REPLACE(email, ‘-‘, ‘a’)) = 0;

The above function is used to validate the email address in Microsoft sql.

User Written function:

CREATE FUNCTION dbo.EmailValidate(@EMAIL varchar(100))

RETURNS bit as

BEGIN

DECLARE @bitRetVal as Bit  IF (@EMAIL <> ” AND @EMAIL NOT LIKE ‘_%@__%.__%’)

SET @bitRetVal = 0  — Invalid

ELSE     SET @bitRetVal = 1   — Valid

RETURN @bitRetVal

END

These are 2 different ways of adding Email validation in MS SQL.

 

Email Validation in mysql:

Using REGEXP  function user can validate the email in mysql also.Follwing statement is useful for email validation in mysql.Make sure that this statement will work only on mysql server not ms sql server.

SELECT * FROM `emails` WHERE `email`NOT REGEXP ‘[-a-z0-9~!$%^&*_=+}{\\\’?]+(\\.[-a-z0-9~!$%^&*_=+}{\\\’?]+)*@([a-z0-9_][-a-z0-9_]*(\\.[-a-z0-9_]+)*\\.(aero|arpa|biz|com|coop|edu|gov|info|int|mil|museum|name|net|org|pro|travel|mobi|[a-z][a-z])|([0-9]{1,3}\\.[0-9]{1,3}\\.[0-9]{1,3}\\.[0-9]{1,3}))(:[0-9]{1,5})?’;

These are some most important ways to validate the email in different database programming languages. Hope you like this article on Email validation in SQL.Please comment in comments section if you have any suggestions.