February 7, 2012 at 11:19 am
Hello All,
I am using EXCEPT in order to get data from table1 wihich doesn't exist in table2.
But the tables are in different databases with different collations.
For this query where should I place collate clause?
I have tried putting it next to each coulmn and to the table, It doesn't work.
Please suggest where the COLLATE clause goes in this query
select A, B from DB..Test1
except
select A, B from DB..Test2
Thanks in advance!
February 7, 2012 at 11:31 am
You can specify it just after the column's name. Here is a small example:
create table test1 (id int, st varchar(30))
go
create table test2 (id int, st varchar(30) collate Latin1_General_CS_AS_KS_WS)
go
insert into test1 (id, st) values (1,'test1')
insert into test1 (id, st) values (2, 'test2')
go
insert into test2 (id, st) values (1,'test')
insert into test2 (id, st) values (2, 'test2')
go
--Should give me an error
select id, st from test1
except
select id, st from test2
go
--Works
select id, st collate Latin1_General_CS_AS_KS_WS from test1
except
select id, st from test2
--Cleanup
drop table test1
go
drop table test2
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
February 7, 2012 at 1:25 pm
Thank you Adi!
February 8, 2012 at 12:06 am
ssc_san (2/7/2012)
For this query where should I place collate clause?
It depends on the rules you want to apply:
DECLARE @T1 TABLE (col1 varchar(30) COLLATE Latin1_General_CI_AS);
DECLARE @T2 TABLE (col1 varchar(30) COLLATE Latin1_General_CS_AS);
INSERT @T1 (col1) VALUES ('a');
INSERT @T2 (col1) VALUES ('A');
SELECT col1
FROM @T1
EXCEPT
SELECT col1 COLLATE Latin1_General_CI_AS
FROM @T2;
SELECT col1 COLLATE Latin1_General_CS_AS
FROM @T1
EXCEPT
SELECT col1
FROM @T2;
SELECT col1 COLLATE DATABASE_DEFAULT
FROM @T1
EXCEPT
SELECT col1 COLLATE DATABASE_DEFAULT
FROM @T2;
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
February 9, 2012 at 11:15 am
Thank you Paul!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply