TSQL function to compare two strings

Question

Can anyone give me some sample TSQL code to compare two email addresses to check they are equal or not?

CLR functions are not an option. I tried that but our DBA for some reason is totally against using CLR functions in SSMS.

I know how to get the domain names (eg: mycompany.com) from the email address.

Really appreciate any suggestions
Thanks in advance

err, you don't need a UDF to compare two strings for equality - what's wrong with using `=` ? — Blorgbeard, Nov 13 '12 at 01:55
Your question says you know how to get the domain names from the email address - so just compare those strings? Or was that a typo and you meant to say you *don't* know? — Blorgbeard, Nov 13 '12 at 02:17

score 10 · Accepted Answer · answered Nov 13 '12 at 06:51

10

Not really sure what you are looking for. From your question, I understand that you need to check 2 email addresses for similarity / dissimilarity.

Why can you not use this?

declare @email1 varchar(100) set @email1 = 'billg@microsoft.com'
declare @email2 varchar(100) set @email2 = 'melinda@microsoft.com'
IF
@email1=@email2
BEGIN
    PRINT 'Same Email'
END
ELSE
BEGIN
    PRINT 'Not Same Email'
END

Raj

answered Nov 13 '12 at 06:51

Raj

10,653
2
45
52

1

Very good simple implementation but does not account for CI vs CS. Use checksum for granular needs, as stated below by Aleksandr. – GoldBishop Oct 24 '16 at 21:23

score 10 · Answer 2 · answered Nov 13 '12 at 09:36

In SQL Server 2005+ use function CHECKSUM()

CHECKSUM computes a hash value, called the checksum, over its list of arguments. The hash value is intended for use in building hash indexes. If the arguments to CHECKSUM are columns, and an index is built over the computed CHECKSUM value, the result is a hash index. This can be used for equality searches over the columns. More info about CHECKSUM()

DECLARE @email1 varchar(100) = 'billg@microsoft.com'
DECLARE @email2 varchar(100) = 'melinda@microsoft.com'
SELECT CASE WHEN CHECKSUM(@email1) = CHECKSUM(@email2) THEN 'Same Email' 
                                                       ELSE 'Different Email' END

Jean-François · Answer 3 · 2017-03-10T20:42:52.657

Though this is an old post, I think it is important to comment about the solution using CHECKSUM. By definition, checksums belong to a finite space thus having a finite number of different values. With a 32 bits number, there are 4,294,967,295 possible values. I am tempted to say there are ONLY 4,294,967,295 possible values leaving place to potential collisions when two email address yield the same checksum. The pure string comparison (email1 = email2) - as suggested by Raj - prevents such collision as by their nature email addresses are unique.

cf: [https://msdn.microsoft.com/en-us/library/ms189788(v=SQL.100).aspx?ranMID=24542&ranEAID=TnL5HPStwNw&ranSiteID=TnL5HPStwNw-pDIZxu_YsTXD2vBagzBEKA&tduid=(e03e708849bdae31622c749be9e951f9)(256380)(2459594)(TnL5HPStwNw-pDIZxu_YsTXD2vBagzBEKA)()][1]

[http://preshing.com/20110504/hash-collision-probabilities/][2]

score 1 · Answer 4 · edited Jan 23 '19 at 13:34

1

--what about patindex() function?
-- small example:

create table t1 (tkey integer, val nvarchar(20) )  
create table t2 (tkey integer, val nvarchar(20) )  
insert into t1 (tkey, val) values (1, 'abc' )  
insert into t1 (tkey, val) values (2, 'efgh' )  
insert into t1 (tkey, val) values (3, 'xyz' )  

insert into t2 (tkey, val) values (1, 'abc' )  
insert into t2 (tkey, val) values (2, '  efgh' )  
insert into t2 (tkey, val) values (3, 'xy z' )  


select t1.val, t2.val, patindex( t1.val, t2.val )  
from t1, t2  
where t1.tkey = t2.tkey

edited Jan 23 '19 at 13:34

DDS

2,340
16
34

answered Jan 23 '19 at 13:00

Torri

11
1

1

Welcome to stack overflow, I'd like to suggest you to format code as code. You can do so selecting the code and press control + k or also indenting it by 4 spaces or by inserting it between two backticks (alt + 96) – DDS Jan 23 '19 at 13:08

score 0 · Answer 5 · answered Nov 13 '12 at 02:23

0

Test whether two email addresses have the same domain:

declare @email1 varchar(100) set @email1 = 'billg@microsoft.com'
declare @email2 varchar(100) set @email2 = 'melinda@microsoft.com'

select 
  case when 
    right(@email1, len(@email1) - charindex('@', @email1)) =
    right(@email2, len(@email2) - charindex('@', @email2))
  then 'Same domain'
  else 'Different domains'
  end

answered Nov 13 '12 at 02:23

Blorgbeard

101,031
48
228
272

Thank you all for your answers.I got what i was looking for – dotnetdev_2009 Nov 13 '12 at 13:10

TSQL function to compare two strings

5 Answers5