string SQL Server 中的 varchar 比较
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2889140/
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
varchar comparison in SQL Server
提问by ram
I am looking for some SQL varchar comparison function like C# string.compare (we can ignore case for now, should return zero when the character expression are same and a non zero expression when they are different)
我正在寻找一些 SQL varchar 比较函数，如 C# string.compare（我们现在可以忽略大小写，当字符表达式相同时应该返回零，当它们不同时返回非零表达式）
Basically I have some alphanumeric column in one table which needs to be verified in another table.
基本上我在一个表中有一些字母数字列需要在另一个表中进行验证。
I cannot do select A.col1 - B.col1 from (query) as "-" operator cannot be applied on character expressions
我不能选择 A.col1 - B.col1 from (query) 因为“-”运算符不能应用于字符表达式
I cannot cast my expression as int (and then do a difference/subtraction) as it fails
我不能将我的表达式转换为 int（然后做一个差异/减法），因为它失败了
select cast ('ASXT000R' as int)
Conversion failed when converting varchar 'ASXT000R' to int
Soundexwould not do it as soundex is same for 2 similar strings
Soundex不会这样做，因为 soundex 对于 2 个相似的字符串是相同的
Differencewould not do it as select difference('abc','ABC')= 4 (as per msdn, difference is the difference in the soundex of 2 character expressions and difference =4 implies least different)
Difference不会像select difference('abc','ABC')= 4那样做（根据 msdn，差异是 2 个字符表达式的 soundex 的差异，而差异 =4 意味着差异最小）
I can write a curson to do ascii comparison for each alphanumeric character in each row, but I was wondering if there is any other way of doing it ?
我可以写一个 curson 来对每一行中的每个字母数字字符进行 ascii 比较，但我想知道是否还有其他方法可以做到这一点？
回答by Mark Byers
we can ignore case for now, should return zero when the character expression are same and a non zero expression when they are different
我们现在可以忽略大小写，当字符表达式相同时应该返回零，当它们不同时应该返回非零表达式
If that's all you want, you can just use this:
如果这就是你想要的，你可以使用这个：
CASE WHEN 'a' = 'b' THEN 0 ELSE 1 END
Note that this expression returns 1 when either of the arguments is NULL. This is probably not what you want. You can use the following to return NULL if either argument is NULL:
请注意，当任一参数为 NULL 时，此表达式返回 1。这可能不是您想要的。如果任一参数为 NULL，您可以使用以下命令返回 NULL：
CASE WHEN 'a' = 'b' THEN 0
WHEN 'a' <> 'b' THEN 1
END
If you want negative and positive values depending on which compares 'larger', you could use this instead:
如果你想要负值和正值取决于哪个比较“更大”，你可以使用这个：
CASE WHEN 'a' < 'b' THEN -1
WHEN 'a' = 'b' THEN 0
WHEN 'a' > 'b' THEN 1
END
回答by etc
select case when a.col1 = b.col1 then 0 else 1 end from a join b on a.id = b.id
or
或者
select case when lower(a.col1) = lower(b.col1) then 0 else 1 end from a join b on a.id = b.id
