TECHNOLOGIES
FORUMS
JOBS
BOOKS
EVENTS
INTERVIEWS
Live
MORE
LEARN
Training
CAREER
MEMBERS
VIDEOS
NEWS
BLOGS
Sign Up
Login
No unread comment.
View All Comments
No unread message.
View All Messages
No unread notification.
View All Notifications
C# Corner
Post
An Article
A Blog
A News
A Video
An EBook
An Interview Question
Ask Question
Join 2 Tables on a Column with Differing Collations in SQL
Kaustubh
Mar 12
2016
Code
1.4
k
0
3
facebook
twitter
linkedIn
Reddit
WhatsApp
Email
Bookmark
expand
SELECT
'SQL_Latin1_General_CP1_CI_AS'
AS
'Collation'
,
COLLATIONPROPERTY(
'SQL_Latin1_General_CP1_CI_AS'
,
'CodePage'
)
AS
'CodePage'
,
COLLATIONPROPERTY(
'SQL_Latin1_General_CP1_CI_AS'
,
'LCID'
)
AS
'LCID'
,
COLLATIONPROPERTY(
'SQL_Latin1_General_CP1_CI_AS'
,
'ComparisonStyle'
)
AS
'ComparisonStyle'
,
COLLATIONPROPERTY(
'SQL_Latin1_General_CP1_CI_AS'
,
'Version'
)
AS
'Version'
UNION
ALL
SELECT
'Latin1_General_CI_AS'
AS
'Collation'
,
COLLATIONPROPERTY(
'Latin1_General_CI_AS'
,
'CodePage'
)
AS
'CodePage'
,
COLLATIONPROPERTY(
'Latin1_General_CI_AS'
,
'LCID'
)
AS
'LCID'
,
COLLATIONPROPERTY(
'Latin1_General_CI_AS'
,
'ComparisonStyle'
)
AS
'ComparisonStyle'
,
COLLATIONPROPERTY(
'Latin1_General_CI_AS'
,
'Version'
)
AS
'Version'
GO
--Clean up previous query
IF EXISTS(
SELECT
1
FROM
sys.tables
WHERE
Name
=
'MyTable1'
)
DROP
TABLE
MyTable1
IF EXISTS(
SELECT
1
FROM
sys.tables
WHERE
Name
=
'MyTable2'
)
DROP
TABLE
MyTable2
--Create a table using collation Latin1_General_CI_AS and add some data to it
CREATE
TABLE
MyTable1
(
ID
INT
IDENTITY(1, 1),
Comments
VARCHAR
(100)
COLLATE
Latin1_General_CI_AS
)
INSERT
INTO
MyTable1 (Comments)
VALUES
(
'Chiapas'
)
INSERT
INTO
MyTable1 (Comments)
VALUES
(
'Colima'
)
--Create a second table using collation SQL_Latin1_General_CP1_CI_AS and add some data to it
CREATE
TABLE
MyTable2
(
ID
INT
IDENTITY(1, 1),
Comments
VARCHAR
(100)
COLLATE
SQL_Latin1_General_CP1_CI_AS
)
INSERT
INTO
MyTable2 (Comments)
VALUES
(
'Chiapas'
)
INSERT
INTO
MyTable2 (Comments)
VALUES
(
'Colima'
)
--Join both tables on a column with differing collations
SELECT
*
FROM
MyTable1 M1
INNER
JOIN
MyTable2 M2
ON
M1.Comments
collate
SQL_Latin1_General_CP1_CI_AS = M2.Comments
collate
SQL_Latin1_General_CP1_CI_AS
GO
join with sql collation