Unicode Substr issue with special characters

Database
Teradata Employee

Unicode Substr issue with special characters

Hi All,

I am facing issue with one of the price column

I have a column defined as 

 Price VARCHAR(50) CHARACTER SET UNICODE NOT CASESPECIFIC,

That has data like 

2070 SEK

2000

500 SEK

üß

500000

Kindly note that üß is not garbage. It is a test case for langauge symbols.

I am using the following sql 

SELECT

CASE WHEN TRIM(price) NOT LIKE '% %'   THEN NULL ELSE TRIM(SUBSTR(TRIM(price),POSITION   (' ' IN TRIM(price)),LENGTH(TRIM(price)))) END AS currency

from cur

to get only number values from the table. 

Its giving me 6706 The string contains untranslatable characters.

I have tried using TRANSLATE((price (VARCHAR(50))) USING latin_to_unicode) but it is not working.

Does any one knows how to work around this thing ?

Regards,

Irfan

5 REPLIES
Teradata Employee

Re: Unicode Substr issue with special characters

Hi All,

I am facing issue with one of the price column

I have a column defined as 

 Price VARCHAR(50) CHARACTER SET UNICODE NOT CASESPECIFIC,

That has data like 

2070 SEK

2000

500 SEK

üß

500000

Kindly note that üß is not garbage. It is a test case for langauge symbols.

I am using the following sql 

SELECT

CASE WHEN TRIM(price) NOT LIKE '% %'   THEN NULL ELSE TRIM(SUBSTR(TRIM(price),POSITION   (' ' IN TRIM(price)),LENGTH(TRIM(price)))) END AS currency

from cur

to get only number values from the table. 

Its giving me 6706 The string contains untranslatable characters.

I have tried using TRANSLATE((price (VARCHAR(50))) USING latin_to_unicode) but it is not working.

Does any one knows how to work around this thing ?

Regards,

Irfan

Senior Apprentice

Re: Unicode Substr issue with special characters

Hi Irfan,

hopefully you want this SQL to split the data in two columns, a VARCHAR is definitely plain wrong.

CASE WHEN POSITION(' ' IN TRIM(price)) > 0 THEN SUBSTRING(TRIM(price) FROM POSITION(' ' IN TRIM(price))+1) END AS currency

You don't need LENGTH, before TD14 it's an ODBC function, in TD14 it's a fastpath-UDF, maybe this causes the error?

What's your TD release? 

Enthusiast

Re: Unicode Substr issue with special characters

Irfan,

Can you please tell what issue exactly you are facing?, I have tried the same code with a little change and it is working fine. The code I used is 

SELECT 

CASE WHEN TRIM(price) NOT LIKE '% %' THEN NULL ELSE
TRIM(SUBSTR(TRIM(price),POSITION (' ' IN TRIM(price)),CHAR_LENGTH(TRIM(price)))) END AS currency

from TableName;

Else you can use 

TRANSLATE((price (VARCHAR(50))) USING UNICODE_TO_LATIN) 

Khurram
Teradata Employee

Re: Unicode Substr issue with special characters

Hi Dieter/Khurram,

The code you gave is working fine. I am using Teradata 14.00.0417. It seems the issue was with LENGTH function i was using.

Thanks for your help

-Irfan

Teradata Employee

Re: Unicode Substr issue with special characters

If you are trying to convert a currency string to a numeric value, then your approach is not internationalized. For example,  if your string was '2 49 Swedish Krona' which includes a radix that is a SPACE (U+0020).

See the character-to-numumeric type conversion in the SQL functions document using an international format clause. The SDF needs to be setup using the tdlocaledef utility. The cast will convert to numeric without added triming, etc.

-David Craig