SQL 比较两个字符串的TSQL函数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13354564/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
TSQL function to compare two strings
提问by Rennish Joseph
Can anyone give me some sample TSQL code to compare two email addresses to check they are equal or not?
谁能给我一些示例 TSQL 代码来比较两个电子邮件地址以检查它们是否相等？
CLR functions are not an option. I tried that but our DBA for some reason is totally against using CLR functions in SSMS.
CLR 函数不是一个选项。我试过了，但我们的 DBA 出于某种原因完全反对在 SSMS 中使用 CLR 函数。
I know how to get the domain names (eg: mycompany.com) from the email address.
我知道如何从电子邮件地址获取域名（例如：mycompany.com）。
Really appreciate any suggestions
Thanks in advance
真的很感激任何建议
提前致谢
回答by Raj
Not really sure what you are looking for. From your question, I understand that you need to check 2 email addresses for similarity / dissimilarity.
不太确定你在找什么。从您的问题中，我了解到您需要检查 2 个电子邮件地址的相似性/差异性。
Why can you not use this?
为什么不能用这个？
declare @email1 varchar(100) set @email1 = '[email protected]'
declare @email2 varchar(100) set @email2 = '[email protected]'
IF
@email1=@email2
BEGIN
PRINT 'Same Email'
END
ELSE
BEGIN
PRINT 'Not Same Email'
END
Raj
拉吉
回答by Aleksandr Fedorenko
In SQL Server 2005+ use function CHECKSUM()
在 SQL Server 2005+ 中使用函数 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()
CHECKSUM 在其参数列表上计算一个哈希值，称为校验和。哈希值旨在用于构建哈希索引。如果 CHECKSUM 的参数是列，并且在计算的 CHECKSUM 值上构建索引，则结果是散列索引。这可用于对列进行相等搜索。有关CHECKSUM() 的更多信息
DECLARE @email1 varchar(100) = '[email protected]'
DECLARE @email2 varchar(100) = '[email protected]'
SELECT CASE WHEN CHECKSUM(@email1) = CHECKSUM(@email2) THEN 'Same Email'
ELSE 'Different Email' END
回答by Blorgbeard is out
Test whether two email addresses have the same domain:
测试两个电子邮件地址是否具有相同的域：
declare @email1 varchar(100) set @email1 = '[email protected]'
declare @email2 varchar(100) set @email2 = '[email protected]'
select
case when
right(@email1, len(@email1) - charindex('@', @email1)) =
right(@email2, len(@email2) - charindex('@', @email2))
then 'Same domain'
else 'Different domains'
end
回答by Jean-Fran?ois
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.
虽然这是一篇旧帖子，但我认为使用 CHECKSUM 评论解决方案很重要。根据定义，校验和属于有限空间，因此具有有限数量的不同值。对于 32 位数字，有 4,294,967,295 个可能的值。我很想说，当两个电子邮件地址产生相同的校验和时，只有 4,294,967,295 个可能的值会导致潜在的冲突。纯字符串比较 (email1 = email2) - 正如 Raj 所建议的 - 防止这种冲突，因为它们的电子邮件地址本质上是唯一的。
[http://preshing.com/20110504/hash-collision-probabilities/][2]
[ http://preshing.com/20110504/hash-collision-probabilities/][2]
回答by Torri
--what about patindex() function?
-- small example:
--patindex() 函数呢？
-- 小例子：
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
