<> doesn't work for String comparision in SQL 2014

Question

<> doesn't work for String comparision in SQL 2014
We've a query similar to below,

Select T1.*
From Table T1
inner Join Table T2 on T1.F1 = T2.F2
Where T2.F3 <> 'Not Available'

Here, always there is no resulting rows.

T2.F3 do have many other string values as well as Null. We need rows of other string values and Null, except this 'Not Available'. Tried many ways to get it, still not succeeded.
Thought there is problem with space in between and tried of the below condition too,

Where Replace(T2.F3, ' ', '') <> 'NotAvailable'
Or
Where T2.F3 Not Like '%Not Available%'

Still nothing works.

Please help!

Thank you!

Answer 1

Did you also try where T2.F3 IS NULL or T2.F3 not like '%Not%Available%'? And check if left join can be used.

Answer 2

Below is the data I took for testing the same. Here '<>' is working :|

DECLARE @T1 Table (F1 INT)
INSERT INTO @T1
Select 11 Union
Select 81 Union
Select 160 Union
Select 161 Union
Select 164 Union
Select 165 Union
Select 166 Union
Select 171 Union
Select 172 Union
Select 173

DECLARE @T2 Table (F2 INT, F3 Varchar(50))
INSERT INTO @T2
Select 11,'Available' Union All
Select 81,'Not Available' Union All
Select 160,'NULL' Union All
Select 161,'NULL' Union All
Select 164,'OTW' Union All
Select 165,'Not Available' Union All
Select 166,'NULL' Union All
Select 171,'Available' Union All
Select 172,'OTW' Union All
Select 173,'NULL'

Select T1.*
From @T1 T1
inner Join @T2 T2 on T1.F1 = T2.F2
Where T2.F3 <> 'Not Available'

Answer 3

Can you produce a repro with DDL and INSERT ?