Email Validation as part of SQL query

[masplin]

Hi

I came across this thread on validating email addresses in SQL and wondered if i could use the same to remove invalid email addresses before importing to the data model.

sql server - TSQL Email Validation (without regex) - Stack Overflow

My current query is

select   distinctINVNUM,Sysdate,Invdate,
Depot,
Customer,
"GROUP",
CTSalut,CTFirstn,CTSurname,Name1,
Addr11,Addr12,Addr13,Addr14,
Pcode1,
Phone1,
CTPhone,
CTMobile,
CTEmail,
Advert,TType,CUSREC,
RegNum,DVMake,DVModel,DVDate,MOTDue,ServiceDue,CTCampaign,CTCampRef
from dbo.VW_InOutStock
where invnum<>'07C38213'
and
invdate<'2015-07-01'

but just adding

ANDWHERE CTEmail LIKE '%_@__%.__%' 
    AND PATINDEX('%[^a-z,0-9,@,.,_]%', REPLACE(CTEmail, '-', 'a')) = 0

says incorrect syntax. Also I don't want to throw out the entire record which I think this would do, but just the CTEmail field to be blank.

any suggestions as how to achieve this appreciated as don't think DAX is as powerful finding these types of errors.

Mike

 

[mole999]

SELECT
distinctINVNUM
,Sysdate
,Invdate
,Depot
,Customer,
"GROUP",
CTSalut,CTFirstn,CTSurname,Name1,
Addr11,Addr12,Addr13,Addr14,
Pcode1,
Phone1,
CTPhone,
CTMobile,
CTEmail,
Advert,TType,CUSREC,
RegNum,DVMake,DVModel,DVDate,MOTDue,ServiceDue,CTCampaign,CTCampRef
FROM dbo.VW_InOutStock
WHERE invnum<>'07C38213'
AND invdate<'2015-07-01'
AND (CTEmail LIKE '%_@__%.__%' 
    AND PATINDEX('%[^a-z,0-9,@,.,_]%', REPLACE(CTEmail, '-', 'a')) = 0)

I THINK

 

[masplin]

Will this exclude the complete record if the criteria isn't met? I'm guessing so as the other where clauses do that. Seems I need something that replaces these emails with a blank if they are found. Is that possible on import?

 

[ImkeF]

Power Query would also be able to do this job I think.
If you're interested, please tell & I will put sth together here.

 

[masplin]

Sorry very late responding on this but got side tracked. your code works with the only downside being it also removed every line where CTEmail is just NULL. I tried all sorts of combinations putting OR in but none work. Any iea how to apply the AND clauses, but leave in the blank ones?

Thanks
Mike