varchar comparison in SQL Server

Question

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)

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

I cannot cast my expression as int (and then do a difference/subtraction) as it fails

select cast ('ASXT000R' as int)
Conversion failed when converting varchar 'ASXT000R'  to int 

Soundex would not do it as soundex is same for 2 similar strings

Difference would 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)

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 ?

Answer 1

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:

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

Answer 2

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