Skip to content

Instantly share code, notes, and snippets.

@csandman
Last active March 4, 2021 22:32
Show Gist options
  • Save csandman/c3d1180d721f1561e26170702458984e to your computer and use it in GitHub Desktop.
Save csandman/c3d1180d721f1561e26170702458984e to your computer and use it in GitHub Desktop.
Check if 2 emails in match in tsql
-- Written for TSQL
CREATE FUNCTION dbo.CompareEmails(
@Email1 AS NVARCHAR(MAX) = '',
@Email2 AS NVARCHAR(MAX) = ''
)
RETURNS BIT
AS
BEGIN
-- Basic check to see if each string is an email
IF LEN(@Email1) = 0
OR LEN(@Email2) = 0
OR CHARINDEX('@', @Email1) < 1
OR CHARINDEX('@', @Email2) < 1
RETURN 0
-- Emails are case insensitive so convert them to lowercase
SET @Email1 = LOWER(@Email1)
SET @Email2 = LOWER(@Email2)
-- Get the index of the '@' characters to split the emails
DECLARE
@AtIndex1 AS int = CHARINDEX('@', @Email1),
@AtIndex2 AS int = CHARINDEX('@', @Email2)
-- Split the emails into front and tail sections for comparison
DECLARE
@Email1Start AS NVARCHAR(MAX) = LEFT(@Email1, @AtIndex1 - 1),
@Email2Start AS NVARCHAR(MAX) = LEFT(@Email2, @AtIndex2 - 1),
@Email1End AS NVARCHAR(MAX) = RIGHT(@Email1, LEN(@Email1) - @AtIndex1),
@Email2End AS NVARCHAR(MAX) = RIGHT(@Email2, LEN(@Email2) - @AtIndex2)
-- In emails, any '.' characters in the username section of an email are optional so remove them
SET @Email1Start = REPLACE(@Email1Start, '.', '')
SET @Email2Start = REPLACE(@Email2Start, '.', '')
DECLARE @Match AS BIT =
CASE WHEN
@Email1Start = @Email2Start
AND @Email1End = @Email1End
THEN 1
ELSE 0
END
RETURN @Match;
END;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment