I wanted a way to validate that an email address in a SQL query was well-formed, and found a lot of resources suggesting to create a scalar function that invokes sp_OACreate among others, and return a bit indicating if it was valid or not.  That works great, but not for those of us in heightened security environments, where granting execute permissions to system stored procs and functions is not permitted.  After searching around I came up with the following function:

CREATE FUNCTION [dbo].[checkEmail]
(
@source nvarchar(5000)
}
RETURNS bit
AS
BEGIN
DECLARE @results bit = 1
IF (SELECT PATINDEX('%_@__%.__%', @source)) = 0
SET @results = 0
RETURN RESULTS
END

This will allow you to pass in a single string, and the return value will be whether or not it’s a good address. For example,

checkEmail(‘someone@someplace.com‘) will return a 1
checkEmail(‘someone@someplace‘) will return a 0

Of course, this isn’t a solution for validating actual domains or if an email address is truly active.  But this will filter out 99% of the mis-keyed data, and ensure it’s well-formed.

Hope this helps someone!