May 22, 2018 at 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!
May 22, 2018 at 10:52 am
Maxer - Tuesday, May 22, 2018 10:40 AMI 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_TitleThis 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?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
May 22, 2018 at 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
?
May 22, 2018 at 12:21 pm
frederico_fonseca - Tuesday, May 22, 2018 11:52 AMalways 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.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply