Comparing two columns of nvarchar data that is identical col1=col2 comes back as false not true?

Question

Comparing two columns of nvarchar data that is identical col1=col2 comes back as false not true?

Maxer

SSCrazy Eights

Points: 8964
More actions
May 22, 2018 at 10:40 am

#352623

I have a table of data that is comparing employee job titles.
This data comes from an external system and is being compared against our existing HR system.
The issue is we look for job title changes, flag them, and process them later.
I am getting a handful of records that appear to have the exact same job titles, but are NOT showing as the same.
SELECT EmplID, HR_Job_Title, External_Job_Title
FROM JobTitleCompareStaging
WHERE HR_JobTitle <> External_Job_Title
This returns multiple rows that APPEAR to be identical.
We tried casting both columns to nvarchar(MAX) or varchar(500) in the WHERE statement thinking perhaps something odd was happening there.
We tested using different collations just incase somehow one column was using a case insensitive collation and the other was not.
I am at a loss here.
What would cause two nvarchar columns to appear identical visually but SQL server marks them as NOT equal?
Thanks!

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

Maxer - Tuesday, May 22, 2018 10:40 AM
I have a table of data that is comparing employee job titles.
This data comes from an external system and is being compared against our existing HR system.
The issue is we look for job title changes, flag them, and process them later.
I am getting a handful of records that appear to have the exact same job titles, but are NOT showing as the same.
SELECT EmplID, HR_Job_Title, External_Job_Title
FROM JobTitleCompareStaging
WHERE HR_JobTitle <> External_Job_Title
This returns multiple rows that APPEAR to be identical.
We tried casting both columns to nvarchar(MAX) or varchar(500) in the WHERE statement thinking perhaps something odd was happening there.
We tested using different collations just incase somehow one column was using a case insensitive collation and the other was not.
I am at a loss here.
What would cause two nvarchar columns to appear identical visually but SQL server marks them as NOT equal?
Thanks!

Try pasting some data from the two columns into Notepad++, and then selecting View/Show Symbol/Show All Characters to see whether there is an 'invisible' difference. Maybe a tab instead of a space?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

frederico_fonseca SSCoach Points: 16154 More actions · Answer 2

always output and visually compare on hex on these cases where it looks the same
(change 500 below as required to hold the content of the fields)
SELECT EmplID , HR_Job_Title , External_Job_Title , convert(varchar(500), HR_Job_Title) as hex_HR_Job_Title , convert(varchar(500), External_Job_Title) as External_Job_Title , convert(varchar(500), ltrim(rtrim(HR_Job_Title))) as hex_HR_Job_Title_trim , convert(varchar(500), ltrim(rtrim(External_Job_Title))) as External_Job_Title_trim FROM JobTitleCompareStaging WHERE HR_JobTitle <> External_Job_Title ?

Maxer SSCrazy Eights Points: 8964 More actions · Answer 3

frederico_fonseca - Tuesday, May 22, 2018 11:52 AM
always output and visually compare on hex on these cases where it looks the same
(change 500 below as required to hold the content of the fields)
SELECT EmplID , HR_Job_Title , External_Job_Title , convert(varchar(500), HR_Job_Title) as hex_HR_Job_Title , convert(varchar(500), External_Job_Title) as External_Job_Title , convert(varchar(500), ltrim(rtrim(HR_Job_Title))) as hex_HR_Job_Title_trim , convert(varchar(500), ltrim(rtrim(External_Job_Title))) as External_Job_Title_trim FROM JobTitleCompareStaging WHERE HR_JobTitle <> External_Job_Title ?

Thanks!

Turns ou there was a new line return in the field.