SQL Comparison of column values not excluding equal values from filter

Question

SELECT 
    DISTINCT
    isnull(dbo.fnRemovePatternFromString(p.Last_Name, '%[,-.'']%'), '')  as [Last]
    ,isnull(dbo.fnRemovePatternFromString(p.First_Name, '%[,-.'']%'), '') as [First]
    ,isnull(upper(fm.ANS_TEXT), '') as [Faculty]
    ,isnull(upper((select fs.ANS_TEXT from dbo.ADD_ANSW fs where fs.ID = p.id and ques_id = 3)),'') as [Billing]

FROM person p
inner JOIN person_facilities pf ON p.ID = pf.ID
LEFT JOIN usr_FacultyMember fm ON p.ID = fm.id
LEFT JOIN dbo.ADD_ANSW fs ON p.ID = fs.id and QUES_ID = 3 or QUES_ID = 71

WHERE 
    pf.Current_status in ('Active')
    and  (UPPER(fm.ANS_TEXT) <> UPPER(fs.ANS_TEXT)); **this compare not working

I would expect the results to exclude ones that have equal values in Faculty and Billing Columns. But the results still look like this:

Last      First Faculty Billing
Skywalker Luke  NON-MFG NON-MFG

I'm having trouble finding anything online about this. Any ideas why it isn't excluding ones that are both NON-MFG? If you look at the original, one is NON-Mfg and the other is NON-MFG, but with Upper case it should compare and be excluded.

**Update: I found some info on <> not always being round trip, so it can't compare correctly... LOWER(x) != LOWER(UPPER(x)). It was suggested to use collate, so I'm trying to find a good example. I'm having trouble applying this example of collate.

score 0 · Answer 1 · answered Jul 24 '19 at 14:37

Apparently your Database is case sensitive. To ignore case sensitivity on those columns, add COLLATE SQL_Latin1_General_CP1_CI_AS right after each one:

SELECT 
 DISTINCT
 isnull(dbo.fnRemovePatternFromString(p.Last_Name, '%[,-.'']%'), '')  as [Last]
,isnull(dbo.fnRemovePatternFromString(p.First_Name, '%[,-.'']%'), '') as [First]
,isnull(upper(fm.ANS_TEXT), '') as [Faculty]
,isnull(upper((select fs.ANS_TEXT from dbo.ADD_ANSW fs where fs.ID = p.id and ques_id = 3)),'') as [Billing]

FROM person p
 inner JOIN person_facilities pf ON p.ID = pf.ID
 LEFT JOIN usr_FacultyMember fm ON p.ID = fm.id
 LEFT JOIN dbo.ADD_ANSW fs ON p.ID = fs.id and QUES_ID = 3 or QUES_ID = 71

WHERE 
 pf.Current_status in ('Active')
 and fm.ANS_TEXT COLLATE SQL_Latin1_General_CP1_CI_AS  <> fs.ANS_TEXT COLLATE SQL_Latin1_General_CP1_CI_AS;

It's still coming up with the same result, with NON-MFG NON-MFG for Faculty and Billing in the rows. — Michele, Jul 24 '19 at 15:35
I did not, but I would consider it after you asking me. I can't help further without an example dataset. — Abel, Jul 24 '19 at 15:35

score 0 · Accepted Answer · answered Jul 26 '19 at 12:52

Decided to handle comparison in the crystal report using the view instead of directly in sql expression for view.

Collate was not working as suggested in other answers, and as in question, <> did not work, and != did not work in the upper(x) comparison.

SQL Comparison of column values not excluding equal values from filter

2 Answers2