[SQL Server] Vérifier la validité d'une adresse mail

  • Imprimer

-- =============================================

-- Author:       Zouhaier KHARROUBI

-- Create date:  20/09/2017

-- Description:  Vérifier la validité d'une adresse mail

-- =============================================

CREATE FUNCTION [dbo].[ufs_AdresseMailValide]

(

   @AddresseMail varchar(500)

)

RETURNS bit

AS

BEGIN

   DECLARE @Result bit

   SET @AddresseMail = LTRIM(RTRIM(@AddresseMail));

   SELECT @Result =

   CASE WHEN

   CHARINDEX(' ',LTRIM(RTRIM(@AddresseMail))) = 0

   AND LEFT(LTRIM(@AddresseMail),1) <> '@'

   AND RIGHT(RTRIM(@AddresseMail),1) <> '.'

   AND LEFT(LTRIM(@AddresseMail),1) <> '-'

   AND CHARINDEX('.',@AddresseMail,CHARINDEX('@',@AddresseMail)) - CHARINDEX('@',@AddresseMail) > 2  

   AND LEN(LTRIM(RTRIM(@AddresseMail))) - LEN(REPLACE(LTRIM(RTRIM(@AddresseMail)),'@','')) = 1

   AND CHARINDEX('.',REVERSE(LTRIM(RTRIM(@AddresseMail)))) >= 3

   AND(CHARINDEX('.@',@AddresseMail) = 0 AND CHARINDEX('..',@AddresseMail) = 0)

   AND(CHARINDEX('-@',@AddresseMail) = 0 AND CHARINDEX('..',@AddresseMail) = 0)

   AND(CHARINDEX('_@',@AddresseMail) = 0 AND CHARINDEX('..',@AddresseMail) = 0)

   AND ISNUMERIC(SUBSTRING(@AddresseMail, 1, 1)) = 0

   AND CHARINDEX(',', @AddresseMail) = 0

   AND CHARINDEX('!', @AddresseMail) = 0

   AND CHARINDEX('-.', @AddresseMail)=0

   AND CHARINDEX('%', @AddresseMail)=0

   AND CHARINDEX('#', @AddresseMail)=0

   AND CHARINDEX('$', @AddresseMail)=0

   AND CHARINDEX('&', @AddresseMail)=0

   AND CHARINDEX('^', @AddresseMail)=0

   AND CHARINDEX('''', @AddresseMail)=0

   AND CHARINDEX('\', @AddresseMail)=0

   AND CHARINDEX('/', @AddresseMail)=0

   AND CHARINDEX('*', @AddresseMail)=0

   AND CHARINDEX('+', @AddresseMail)=0

   AND CHARINDEX('(', @AddresseMail)=0

   AND CHARINDEX(')', @AddresseMail)=0

   AND CHARINDEX('[', @AddresseMail)=0

   AND CHARINDEX(']', @AddresseMail)=0

   AND CHARINDEX('{', @AddresseMail)=0

   AND CHARINDEX('}', @AddresseMail)=0

   AND CHARINDEX('?', @AddresseMail)=0

   AND CHARINDEX('<', @AddresseMail)=0

   AND CHARINDEX('>', @AddresseMail)=0

   AND CHARINDEX('=', @AddresseMail)=0

   AND CHARINDEX('~', @AddresseMail)=0

   AND CHARINDEX('`', @AddresseMail)=0

   AND CHARINDEX('.', SUBSTRING(@AddresseMail, CHARINDEX('@', @AddresseMail)+1, 2))=0

   AND CHARINDEX('.', SUBSTRING(@AddresseMail, CHARINDEX('@', @AddresseMail)-1, 2))=0

   AND LEN(SUBSTRING(@AddresseMail, 0, CHARINDEX('@', @AddresseMail)))>1

   AND CHARINDEX('.', REVERSE(@AddresseMail)) > 2

   AND CHARINDEX('.', REVERSE(@AddresseMail)) < 5

   THEN 1 ELSE 0 END

   RETURN @Result

END