CODEWITHSUNDEEP
sql-servert-sqlcollation
BogdanM
BogdanM

Reputation: 997

How to check 'AAA' vs 'AAa' in TSQL using SQL_Latin1_General_CP1_CI_AS collation

I've a problem and I just cannot seem to get around it. My database has the above collation setting, SQL_Latin1_General_CP1_CI_AS, which I cannot change and I have for one column which I imported from another database which has SQL_Latin1_General_CP1_CS_AS, both the values 'AAA' and 'AAa'. Further more by joining on this field I have to retrieve from another table (same collation as my db, SQL_Latin1_General_CP1_CI_AS), the value for another column. My problem is that due to this collation, SQL_Latin1_General_CP1_CI_AS, both value ('AAA' and 'AAa'), are "seen" as the same and my join returns value for both 'AAA' and 'AAa' where it should return a matching join only for 'AAa'. Is there a "trick" which could help me to filter only the 'AAa's ? meaning to emulate somehow the SQL_Latin1_General_CP1_CS_AS collation? Regards,

LE: I have two tables, Table1 and Table2. Table1 has the column1 - ID, column2- currency. The Table2 has the columns column1-currency and the column2 - rate. both columns, from table1 and table2 have values which are insensitive (eg. EUR and EUr). I want to retrieve the value from table2, the rate value, only for the rows which match the exact currency. I've tried 

Select t1.id
, t1.currency
, t2.rate 
from table1 t1 
inner join table2 t2 on t1.currency=t2.currency COLLATE  SQL_Latin1_General_CP1_CS_AS

But it;s not working as, for the ids which are have EUR i got the rate, although I should have only the ids which are only having EUr as rate.

Upvotes: 2

Views: 415

Answers (2)

SqlZim
SqlZim

Reputation: 38053

select * 
from t
where col collate SQL_Latin1_General_CP1_CS_AS = 'AAa'

rextester demo: http://rextester.com/CZAWR50665

returns AAa from this test setup:

create table t (col varchar(32))
insert into t values
('AAA'),('AAa'),('aAa'),('AaA')

For a join, you could use collate like so: 

select * 
from t
  inner join t as t2
    on t.col collate SQL_Latin1_General_CP1_CS_AS  = t2.col;

returns

+-----+-----+
| col | col |
+-----+-----+
| AAA | AAA |
| AAa | AAa |
| aAa | aAa |
| AaA | AaA |
+-----+-----+

Upvotes: 4

Jayasurya Satheesh
Jayasurya Satheesh

Reputation: 8043

Usually, SQL Server is not case sensitive(Just Like most other SQL Languages - MySQL has a feature to enable or disable case sensitive feature. refer-> Is SQL syntax case sensitive?). So if you are using SQL Server then there might be some other issue in the data like some invalid characters. like char(9) or char(10) etc. But if you are sure that the issue is the case, not anything else then Try joining the values by converting the case of both files to either upper or Lower Case. Something like the below

SELECT
   *
FROM table1 t1
     INNER JOIN table2 t2
ON UPPER(t1.Colname) = UPPER(t2.Colname)

Upvotes: -1

Related Questions