2

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.

HappyTown
  • 6,036
  • 8
  • 38
  • 51
BogdanM
  • 957
  • 4
  • 15
  • 32
  • 3
    You typically do this via an inline collate statement. You can do that in a join, where clause etc. https://msdn.microsoft.com/en-us/library/ms184391.aspx – Sean Lange Mar 13 '17 at 18:55
  • I've tried using for the join collumn COLLATE SQL_Latin1_General_CP1_CS_AS but it not seems work – BogdanM Mar 13 '17 at 18:56
  • Possible duplicate of [How to get a case sensitive version of a collation in SQL Server?](http://stackoverflow.com/questions/28920584/how-to-get-a-case-sensitive-version-of-a-collation-in-sql-server) – Cee McSharpface Mar 13 '17 at 18:57
  • 1
    Can you define "it not seems work"? Sharing some code would go a LONG way here. As it stands we have vague description of the problem with a response that "it doesn't work". – Sean Lange Mar 13 '17 at 19:01
  • Ok you have vaguely defined that you two tables....how about some actual details to the problem at hand? – Sean Lange Mar 13 '17 at 19:09
  • @SeanLange: i've edited the main question. – BogdanM Mar 13 '17 at 19:09
  • 1
    Instead of all the verbiage, i'd suggest some sample data (ideally a DDL and MCVE). Copy something similar to @SqlZim if his answer is sufficient – S3S Mar 13 '17 at 19:09
  • I edited your question so that at least the query is legible. You need to have the collation on the left side table (t1 in this case), not the right side table. – Sean Lange Mar 13 '17 at 19:11

2 Answers2

4
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 |
+-----+-----+
SqlZim
  • 37,248
  • 6
  • 41
  • 59
  • thanks, it worked...i had one additional clause to add to table t2 and that was why I was the impression the join didn't work – BogdanM Mar 13 '17 at 19:27
  • Yup - keep in mind that `collate` only applies to the _immediately preceeding_ portion, not to the entire comparison – Dan Field Mar 13 '17 at 19:59
-1

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)
Community
  • 1
  • 1
Jayasurya Satheesh
  • 7,826
  • 3
  • 22
  • 39
  • 1
    This will only work if the collation is a case sensitive collation. In a case insensitive collation 'AAA' = 'aaa'. And simply forcing everything to upper is not a good approach because maybe everything isn't all upper case. – Sean Lange Mar 13 '17 at 19:01