Quelle stammt IMHO von NovickSoftware.com
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF EXISTS (select name, * from sysobjects where name = 'fod_IsEmailValid' and Type = 'FN') Drop function fod_IsEmailValid
GO
CREATE FUNCTION [dbo].[fod_IsEmailValid] ( @EmailAddr varchar(255))
RETURNS BIT
/*
* Check a Emailaddress
* Checks the text string to be sure it's a valid e-mail address.
* Returns 1 when it is, otherwise 0.
* 1 if @EmailAddr is a valid email address
* Example:
SELECT CASE WHEN 1=dbo.gc_IsEmailValid('anovick@NovickSoftware.com')
THEN 'Is an e-mail address' ELSE 'Not an e-mail address' END
*
* Test:
print case when 1=dbo.udf_txt_isEmail('anovick@novicksoftware.com')
then 'Passes' else 'Fails' end + ' test for good addr'
print case when 0=dbo.udf_txt_isEmail('@novicksoftware.com')
then 'Passes' else 'Fails' end + ' test for no user'
print case when 0=dbo.udf_txt_isEmail('anovick@n.com')
then 'Passes' else 'Fails' end + ' test for 1 char domain'
print case when 1=dbo.udf_txt_isEmail('anovick@no.com')
then 'Passes' else 'Fails' end + ' test for 2 char domain'
print case when 0=dbo.udf_txt_isEmail('anovick@.com')
then 'Passes' else 'Fails' end + ' test for no domain'
print case when 0=dbo.udf_txt_isEmail('anov ick@novicksoftware.com')
then 'Passes' else 'Fails' end + ' test for space in name'
print case when 0=dbo.udf_txt_isEmail('ano#vick@novicksoftware.com')
then 'Passes' else 'Fails' end + ' test for # in user'
print case when 0=dbo.udf_txt_isEmail('anovick@novick*software.com')
then 'Passes' else 'Fails' end + ' test for * asterisk in domain'
****************************************************************/
AS BEGIN
DECLARE @AlphabetPlus VARCHAR(255)
, @Max INT -- Length of the address
, @Pos INT -- Position in @EmailAddr
, @OK BIT -- Is @EmailAddr OK
-- Check basic conditions
IF @EmailAddr IS NULL
OR NOT @EmailAddr LIKE '_%@__%.__%'
OR CHARINDEX(' ',LTRIM(RTRIM(@EmailAddr))) > 0
RETURN(0)
SELECT @AlphabetPlus = 'abcdefghijklmnopqrstuvwxyz01234567890_-.@'
, @Max = LEN(@EmailAddr)
, @Pos = 0
, @OK = 1
WHILE @Pos < @Max AND @OK = 1 BEGIN
SET @Pos = @Pos + 1
IF NOT @AlphabetPlus LIKE '%'
+ SUBSTRING(@EmailAddr, @Pos, 1)
+ '%'
SET @OK = 0
END -- WHILE
RETURN @OK
END
go
--?????
--GRANT EXEC on dbo.fod_IsEmailValid to PUBLIC
go
