Email Validation

UDA
Enthusiast

Email Validation

Hi Guys

Can you please help me convert this function into Teradata function, This functions checks if the email is valid and return the correct email,

See the function below:

1.
CREATE FUNCTION EMAILVALIDATE (@email varChar(100))
RETURNS int
AS
BEGIN
DECLARE @invalChars varchar(5),@valid int,@badChar varchar(1),@atPos int,@periodPos int
SET @valid = 1
SET @invalChars = ' /:,;'
--Check to see if it's blank
IF len(ltrim(rtrim(@email))) = 0
SET @valid = 0
ELSE
--Loop invalid characters to see if it exists in email
WHILE len(@invalChars) > 0
BEGIN
SET @badChar = substring(@invalChars,1,1)
IF(charindex(@badChar,@email) > 0)
--If invalid character was found, return 0 to invalidate
SET @valid = 0
SET @invalChars = replace(@invalChars,@badChar,'')
END
--Check to see if "@" exists.
SET @atPos = charindex('@',@email,1)
IF @atPos = 0
SET @valid = 0
--Check to see if extra "@" exists after 1st "@".
IF charindex('@',@email,@atPos+1) > 0
SET @valid = 0
SET @periodPos = charindex('.',@email,@atPos)
IF @periodPos = 0
SET @valid = 0
IF (@periodPos+3) > len(@email)
SET @valid = 0
RETURN (@valid)
END

I have also tried this method in Teradata, the problem with this method is that it cannot mark the following email (ince@gmail.c) as invalid, while the correct email would be ince@gmail.com or ince@gmail.co.za, see the function below

2.
WHEN TRIM(Electronic_Address_Txt) NOT LIKE '%_@__%.__%'
OR TRIM(Electronic_Address_Txt) LIKE ANY
('%/%' ,'%/' ,'/%' ,'%\%' ,'%\' ,'\%' ,'%:%' ,'%:' ,':%' ,'%,%' ,'%,' ,',%' ,'%,%' ,'%;%' ,';%' ,'%;' ,'%~%' ,'~%' ,'%~' ,'%!%' ,'!%' ,'%!' ,'%#%' ,'#%' ,'%#' ,'%$%' ,'$%' ,'%$'
, '%@%@%' ,'@%' ,'%@' ,'.%' ,'%.' ,'-%' ,'%-' ,'%=%' ,'=%' ,'%=' ,'%+%' ,'+%' ,'%+' ,'%|%' ,'|%' ,'%|' ,'%{%' ,'{%' ,'%{' ,'%}%' ,'}%' ,'%}' ,'%[%' ,'[%' ,'%[' ,'%]%' ,']%' ,'%]'
,'%`%' ,'`%' ,'%`' , '%^%' ,'^%' ,'%^' ,'%&%' ,'&%' ,'%&' ,'%*%' ,'*%' ,'%*' ,'%(%' ,'(%' ,'%(' ,'%)%' ,')%' ,'%)' ,'% %','%"%','"%','%"','% %','%..%')
THEN 'N'
ELSE 'Y'
END AS Valid_Email_Ind

2 REPLIES
rgs
Enthusiast

Re: Email Validation

If you are allowed to write C language UDFs on your system here is one solution. I hate that this forum does not retain the proper indentation, so you will have to just read it the hard way.

/*
REPLACE FUNCTION emailvalidate(VARCHAR(100))
RETURNS INTEGER
LANGUAGE C
NO SQL
PARAMETER STYLE TD_GENERAL
EXTERNAL;

The C file source named emailvalidate.c
*/

#define SQL_TEXT Latin_Text
#include "sqltypes_td.h"
#include "string.h"

void emailvalidate(CHARACTER *emailstr,
INTEGER *valid,
char sqlstate[6])

{
char *e_str;
int found_at = 0;
int found_bfat = 0;
int found_aftat = 0;
int found_aftdot = 0;

/* Pointer to string */
e_str = emailstr;

/* scan the input string a character at a time */
while(*e_str)
{
/* check for valid characters */
switch (*e_str)
{
case '/':
case ':':
case ',':
case ';':
*valid = 0;
return;
}

/* check for @ */
if (*e_str == '@')
{
/* we already found @ then return not valid */
if (found_at)
{
/* more than one @ */
*valid = 0;
return;
}
/* found a @ */
found_at = 1;
/* next character from top */
e_str++;
continue;
}

/* is this the pesky dot */
if (*e_str == '.')
{
/* if we have not found any valid characters before @ or */
/* character after @ mark as not valid */
if(!found_aftat || !found_bfat)
{
*valid = 0;
return;
}

/* found that dot -- make sure there are at least 3 */
/* characters after it */
if (found_aftat && (strlen(e_str) < 4 ))
{
*valid = 0;
return;
}

/* if we are after the @, mark that we found at least one */
/* dot. Needed in case there are no dots which would */
/* be a problem later */
if (found_aftat)
found_aftdot = 1;
/* next character */
e_str++;
continue;

}

/* get here an ordinary character */

/* count characters before @ and after @ */
if (!found_at)
found_bfat++;
else
found_aftat++;

/* next character */
e_str++;
}

/* see what we ended up with. It must have valid characters */
/* before @ and after the @ and we must have found at least one */
/* dot in the part after the @ */
if (!found_bfat || !found_aftat || !found_aftdot)
*valid = 0;
else
*valid = 1;

return;
}

Some results:

Select emailvalidate('abc@my.com')
---------------------------
1

Select emailvalidate('abc@my.c,om')
----------------------------
0

Select emailvalidate('ac@b.test.com')
------------------------------
1

Select emailvalidate('abc@.com')
-------------------------
0
Select emailvalidate('abc@com')
------------------------
0
Enthusiast

Re: Email Validation

Hi

Unfortunately Im using Teradata SQL Assistant, we dont have C software in house.

Thanks,
Ince