Need to convert UNICODE to LATIN

Database

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 DATA

or

CREATE 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 DATA

In 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.

Thanks

Brad

4 REPLIES
Junior Contributor

Re: Need to convert UNICODE to LATIN

SQL Reference: Data Types and Literals
Chapter 2: Data Literals
Character Data Literals

The 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

Re: Need to convert UNICODE to LATIN

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
Enthusiast

Re: Need to convert UNICODE to LATIN

CAST(COALESCE(col1,'') AS VARCHAR(10))

produces UNICODE

While 

COALESCE(CAST(col1 AS VARCHAR(10)),'') 

produces LATIN.

Any explaination for this?

Thanks.

Enthusiast

Re: Need to convert UNICODE to LATIN

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.