Creating subset of data containing Chinese characters

Database

Creating subset of data containing Chinese characters

Hi,

I am working on a data set in SQL Assistant that contains Chinese characters. I am using UTF-16 as Session Character Set to read the Chinese characters. However, an issue that I am facing is related to creating a subset of the data by using where condition.

For instance, I have a column called market which has null values and 'CHINA ' (trimming doesn't help in rendering 'CHINA ' to 'CHINA').

where market='CHINA ' returns 0 entries

where market='CHINA' RETURNS 0 ENTRIES

where market is not null gives correct result

where market like '%CHINA%' gives correct result

Can anyone please help out if this is quite common and if there is a workaround.

Thanks a lot,

Tanay

Tags (1)
6 REPLIES
N/A

Re: Creating subset of data containing Chinese characters

Hi Tanay,

looks like there's something else in your market column.

Check the actual data with SELECT CHAR2HEXINT(market) ... where market like '%CHINA%', for 'CHINA' it should return '004300480049004E0041'

Re: Creating subset of data containing Chinese characters

Thanks for the help, actually it's CN not CHINA

SELECT CHAR2HEXINT(market) ... where market like '%CN%' returns 0043004E0000

N/A

Re: Creating subset of data containing Chinese characters

Hi Tanay, 

looks like market is a CHAR(3), there's a trailing blank.

But for this data both conditions should return data, too:

where market='CN ' 
where market='CN'

Can you check DBQL which source code was actually sent to the DBMS?

What's that step in Explain?

Re: Creating subset of data containing Chinese characters

I did a help table ... , here is the output for market:





Column Name Market
Type CV
Comment ?
Nullable Y
Format X(254)                        
Title ?
Max Length 508
Decimal Total Digits ?
Decimal Fractional Digits ?
Range Low ?
Range High ?
UpperCase N
Table/View? T
Default value ?
Char Type 2
IdCol Type ?
UDT Name ?
Temporal N

Re: Creating subset of data containing Chinese characters

A Column is having chinese characters.  I need to select rows from a table which is having chinese characters, Can some one pls help me how i can

select these type of data.

Teradata Employee

Re: Creating subset of data containing Chinese characters

Hi Divya,

I need some clarification of your question. Are you asking how to detect (e.g., with a SQL function/UDF) if a Unicode character column contains any Chinese Ideograhic script characters?

Thanks,

-Dave