SQL Collation conflict when comparing to a column in a temp table

sql collation
Justin picture Justin · Sep 10, 2009 · Viewed 30.5k times · Source

I have a SQL query that compares a value in the database to a constant:

SELECT * FROM my_table
INNER JOIN #TempTable tem
    ON my_table.id = temp.id
    AND my_table.key = 'SOME STRING'

And I get the error:

Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.

How can I get around this? (without making changes to the database)

UPDATE: I get this error even if I remove the last like (the string comparison)...

Answer

Quassnoi picture Quassnoi · Sep 10, 2009

Seems your id's are VARCHARs with different collations.

Try this:

SELECT  *
FROM    my_table
INNER JOIN
        #TempTable tem
ON      my_table.id = temp.id COLLATE SQL_Latin1_General_CP1_CI_AS
        AND my_table.key = 'SOME STRING'

Related questions

How do I see what character set a MySQL database / table / column is?

What is the (default) charset for: MySQL database MySQL table MySQL column

Read More
#1273 – Unknown collation: ‘utf8mb4_unicode_520_ci’

I have a WordPress website on my local WAMP server. But when I upload its database to live server, I get error #1273 – Unknown collation: ‘utf8mb4_unicode_520_ci’ Any help would be appreciated!

Read More
How to change the default collation of a table?

create table check2(f1 varchar(20),f2 varchar(20)); creates a table with the default collation latin1_general_ci; alter table check2 collate latin1_general_cs; show full columns from check2; shows the individual collation of the columns as 'latin1_general_ci'. …

Read More