CODEWITHSUNDEEP
mysqlutf-8utf-16
Yoosuf
Yoosuf

Reputation: 892

Converting UTF16 to UTF8 in MySQL

I tried to execute the following query

SELECT  Scania.GensetType, Scania.EngineType, Scania.Engine60Hz, Scania.Alternator, Scania.PriceEur
FROM Scania  
LEFT JOIN NordhavnGenset
    ON Scania.GensetType=NordhavnGenset.Alternator 
LEFT JOIN Generator
    ON Scania.Alternator=Generator.Alternator
LEFT JOIN Insulation 
    ON NordhavnGenset.Insulation=Insulation.Insulation
LEFT JOIN Klasse
    ON NordhavnGenset.Class=Klasse.Class
LEFT JOIN AirInletFilter
    ON NordhavnGenset.AirInletFilter=AirInletFilter.AirInletFilter
LEFT JOIN IP
    ON NordhavnGenset.Ip=IP.IP
WHERE (NordhavnGenset.MaxKva='46') and (Generator.Alternator='ECP34-1LN/4') and (Insulation.Insulation='F (90ºC/45ºC)') and (Klasse.Klasse='KRS\r') and (AirInletFilter.AirInletFilter='No') and (IP.IP='IP23');

However it appears that 

Scania.Alternator=Generator.Alternator

have data type of text UTF 16 and text UTF8 respectively. As a result, it cannot be executed. I need to know the syntax that should be used for making this conversion

This is the error message I get when executing the query

#1267 - Illegal mix of collations (utf16_unicode_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation '='

Upvotes: 1

Views: 2799

Answers (1)

Joni
Joni

Reputation: 111259

You can use the CONVERT(.. USING ..) function to convert values from one character encoding to another:

Scania.Alternator = CONVERT(Generator.Alternator USING ucs2)

Alternatively you could look into why the column encodings are different and consider using either utf8 or ucs2 for everything...

Upvotes: 2

Related Questions