All Forums Database
bswarmer 3 posts Joined 01/06
06 Jan 2006
Need to convert UNICODE to LATIN

I have encountered numerous problems due to differing character sets in my database. The biggest issue encountered is a failure in Informatica ETL when attempting to export data from Teradata. LATIN is the default character set; however certain instances exist where columns default to UNICODE instead. CREATE TABLE DATABASE.TABLE_A, NO FALLBACK AS( SELECT 'YES' AS DUMMY_FLAG FROM TABLE_B)WITH DATAorCREATE TABLE DATABASE.TABLE_A, NO FALLBACK AS( SELECT CASE WHEN A IS NULL THEN 'YES' ELSE 'NO' END AS DUMMY_FLAG FROM TABLE_B)WITH DATAIn certain scripts when a literal is used in the select statement, the column defaults to UNICODE. I need to know how to convert the existing columns back to LATIN, and what can be done to prevent this in the future. ThanksBrad

dnoeth 4628 posts Joined 11/04
08 Jan 2006

SQL Reference: Data Types and LiteralsChapter 2: Data LiteralsCharacter Data LiteralsThe data type and server character set of a character literal is always VARCHAR(n)CHARACTER SET UNICODE, where n is the length of the literal.SELECT 'YES' AS DUMMY_FLAG_U, type (dummy_flag_U), TRANSLATE('YES' USING UNICODE_TO_LATIN) AS DUMMY_FLAG, type (dummy_flag);Dieter

Dieter

bswarmer 3 posts Joined 01/06
09 Jan 2006

Thank you Dieter, that is exactly what I needed. This will resolve any future occurances (after I update hundreds of scripts of course). As for the existing tables, is there a way to ALTER the character set in a table, or is rebuilding every table using this TRANSLATE function the only way? This will work, however many of my tables are just massive. Thanks again

virenag 6 posts Joined 09/12
08 Oct 2012

CAST(COALESCE(col1,'') AS VARCHAR(10))
produces UNICODE
While 
COALESCE(CAST(col1 AS VARCHAR(10)),'') 
 
produces LATIN.
 
Any explaination for this?
 
Thanks.
 

jinli 10 posts Joined 11/12
08 Nov 2012

not sure from where you concluded that.
here literal-strings are being discussed.
see --
select type(f01), type(f02) from (select coalesce(cast('yes' as varchar(10)), '') as f01, cast(coalesce('yes', '') as varchar(10)) as f02) as t;

 *** Query completed. One row found. 2 columns returned.
 *** Total elapsed time was 1 second.

Type(f01)                                                                       Type(f02)
------------------------------------------------------------------------------  ------------------------------------------------------------------------------
VARCHAR(10) CHARACTER SET UNICODE                                               VARCHAR(10) CHARACTER SET UNICODE
 
both are unicode type.

You must sign in to leave a comment.