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)
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)
Is there any other way of doing it ?