-1

I am using SQL to compare two columns and return TRUE/FALSE if they are equal.

In some cases, the two columns contain exactly the same string (no spaces or anything) but I am still getting false.

What may the reason for this be?

I am using this code: 

CASE WHEN column1 = column2 THEN 0 ELSE 1 END AS [check]
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Data_x99
  • 39
  • 1
  • 6
  • convert both columns to lower case, and trim the whitespaces. You can also check the string length for both columns to see if there is any differences between them (maybe a whitespace). – iSR5 Jul 21 '19 at 08:40
  • You should compare the strings by pasting both of them to Notepad++ and enabling "Show invisible characters". They are not the same, they look the same – Lukasz Szozda Jul 21 '19 at 08:48
  • Another example they may use the same gliph but are different: https://stackoverflow.com/a/36424445/5070879 – Lukasz Szozda Jul 21 '19 at 08:48

2 Answers2

2

The values are different despite the displayed value.

Using T-SQL, run a query like this to see the exact difference in the underlying raw values:

SELECT
     column1
    , CAST(column1 AS varbinary(MAX)) AS column1Binary
    , column2
    , CAST(column2 AS varbinary(MAX)) AS column2Binary
FROM dbo.YourTable;

This will reveal underlying differences like tabs or subtle character differences.

Dan Guzman
  • 43,250
  • 3
  • 46
  • 71
1

In fact, a likely explanation for what you are seeing is that one/both of the strings has leading and/or trailing whitespace. On SQL Server you may try:

CASE WHEN LTRIM(column1) = LTRIM(column2) THEN 0 ELSE 1 END AS [check]

If the above does not detect the problematical records, then try checking the length:

CASE WHEN LEN(column1) = LEN(column2) THEN 0 ELSE 1 END AS [check2]
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • [SQL Server ignores trailing spaces when comparing `varchar` values, but not leading spaces.](https://rextester.com/OML35631) – Zohar Peled Jul 21 '19 at 09:09
  • Then maybe there are hidden characters which you can't see. Check the `LEN` of both strings. Or, there could be characters which look similar but are really not the same. – Tim Biegeleisen Jul 21 '19 at 10:00
  • Note - tsql now has trim function to remove both leading and trailing spaces! – SMor Jul 21 '19 at 13:05