in Tsql can i compare two string "MY String" to my string and show they are different

Question

I need to do a query between two tables and find non matching fields

table 1 field locations has "my String"    
table 2 field locations has "MY string"

they = by text but not by capitalization i need to return a false for this

alternatively you can cast each field to varbinary for the comparison — Gavin, May 08 '14 at 18:32
It's a matter of setting Collation in your query - check http://stackoverflow.com/questions/3969059/sql-case-sensitive-string-compare for details. — Gabriel M, May 08 '14 at 18:36

score 0 · Answer 1 · answered May 08 '14 at 18:41

Having the following data:

DECLARE @TableOne TABLE
(
     [ID] TINYINT
    ,[Value] VARCHAR(12)
)

DECLARE @TableTwo TABLE
(
    [ID] TINYINT
   ,[Value] VARCHAR(12)
)

INSERT INTO @TableOne ([ID], [Value])
VALUES (1,'my String')

INSERT INTO @TableTwo ([ID], [Value])
VALUES (1,'MY String')

You can use set Case Sentitive collation like this:

SELECT [TO].[Value]
      ,[TW].[Value]
FROM @TableOne [TO]
INNER JOIN @TableTwo [TW]
    ON [TO].[ID] = [TW].[ID]
    AND [TO].[Value] <> [TW].[Value]
COLLATE Latin1_General_CS_AS

or use HASH functions like this:

SELECT [TO].[Value]
      ,[TW].[Value]
FROM @TableOne [TO]
INNER JOIN @TableTwo [TW]
    ON [TO].[ID] = [TW].[ID]
WHERE HASHBYTES('SHA1', [TO].[Value]) <> HASHBYTES('SHA1', [TW].[Value])

I need to do a left join to find the missing values id @table2 would have a null for "my String" — user2529573, May 08 '14 at 18:58
@user2529573 If you have issues implementing this in your real case, plaease edit your questions adding more details of your tables structure. — gotqn, May 08 '14 at 19:33

score 0 · Answer 2 · answered May 08 '14 at 18:41

DECLARE @Table1 AS TABLE (FieldName VARCHAR(100))
DECLARE @Table2 AS TABLE (FieldName VARCHAR(100))

INSERT INTO @Table1 (FieldName) VALUES ('MY Location')
INSERT INTO @Table2 (FieldName) VALUES ('My Location')

With a default case insensitive collation order - Matches and returns results

SELECT * FROM @Table1 AS T1 
INNER JOIN @Table2 AS T2 
ON T1.FieldName = T2.FieldName

With a case sensitive collation order specified. Will not match

SELECT * FROM @Table1 AS T1 
INNER JOIN @Table2 AS T2 
ON T1.FieldName = T2.FieldName  COLLATE Latin1_General_CS_AS_KS_WS

Microsoft article on collation

in Tsql can i compare two string "MY String" to my string and show they are different

2 Answers2