USE [P21]
GO

/****** Object:  UserDefinedFunction [dbo].[nutech_fn_ChkValidEmail]    Script Date: 9/23/2024 6:43:32 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author: NuTech Services, LLC.
-- Create date: 12/01/2021
-- Description:	Pass one or more email address separated by semicolon will return
--              1 if ALL are valid or 0 if any one is invalid
-- =============================================
CREATE FUNCTION [dbo].[nutech_fn_ChkValidEmail]
(
    @EmailStr varchar(250)
)
RETURNS INT
AS
BEGIN

  DECLARE @ValidEmail as int = 1
  DECLARE @pos as int = 0
  DECLARE @len as int = 0
  DECLARE @EmailAddr as varchar(250)

  IF RIGHT(@EmailStr,1) <> ';'
    SET @EmailStr = @EmailStr + ';'

  WHILE CHARINDEX(';', @EmailStr, @pos+1) > 0
  BEGIN
    SET @len = CHARINDEX(';', @EmailStr, @pos+1) - @pos
	SET @EmailAddr = SUBSTRING(@EmailStr, @pos, @len)
	SET @EmailAddr = RTRIM( LTRIM( @EmailAddr ) )
    SET @ValidEmail = case when @EmailAddr like '% %' then 0
                           when @EmailAddr like ('%["(),:;<>\]%') then 0
                           when substring(@EmailAddr,charindex('@',@EmailAddr),len(@EmailAddr)) like ('%[!#$%&*+/=?^`_{|]%') then 0
                           when left(@EmailAddr,1) like ('[-_.+]') then 0
                           when right(@EmailAddr,1) like ('[-_.+]') then 0
                           when @EmailAddr like '%[%' then 0
                           when @EmailAddr like '%]%' then 0
                           when @EmailAddr like '%@%@%' then 0
                           when @EmailAddr NOT LIKE '_%@_%._%' then 0
	                  else 1
				      end
	  IF @ValidEmail = 0
	    RETURN @ValidEmail
      SET @pos = CHARINDEX(';', @EmailStr, @pos+@len) + 1
	END 

	RETURN @ValidEmail
END
GO


