Equivalent sentence in Teradata for the Oracle "CONVERT"?

Database
Enthusiast

Equivalent sentence in Teradata for the Oracle "CONVERT"?

Hello everybody,

I can't find a sentence to make the same as "CONVERT" does in Oracle. For example:

  • select CONVERT('José se comprò un camiôn', 'US7ASCII', 'WE8ISO8859P1') from DUAL; --> Jose se compro un camion
  • select CONVERT('Gta. Pérez Cidón 1; 3º B', 'US7ASCII', 'AL32UTF8') from DUAL; --> Gta. Perez Cidon 1; 3? B

​So I would like to know what I could use to make US7ASCII to WE8ISO8859P1 and US7ASCII to AL32UTF8 text conversions.

Thanks in advance and best regards

Juan
4 REPLIES
Supporter

Re: Equivalent sentence in Teradata for the Oracle "CONVERT"?

Not sure that this will work in TD - not all character sets are supported. Usually you store international data as unicode.

Did you check the Translate function?

Depending on you DB release you can also check the python script option...

Teradata Employee

Re: Equivalent sentence in Teradata for the Oracle "CONVERT"?

Hi Juan,

The data type of character literals is in Teradata is always UNICODE. There is no need to convert/translate them. You can verify the characters are in Latin with the _Latin prefix. The default is _Unicode if it is missing. For example with the UTF8 character set:

select 'José se comprò un camiôn';
select 'Gta. Pérez Cidón 1; 3º B';
select _latin'José se comprò un camiôn';
select _latin'Gta. Pérez Cidón 1; 3º B';

select 'José se comprò un camiôn';

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

'José se comprò un camiôn'
------------------------------------------------------------------------
José se comprò un camiôn

 BTEQ -- Enter your SQL request or BTEQ command:
select 'Gta. Pérez Cidón 1; 3º B';

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

'Gta. Pérez Cidón 1; 3º B'
---------------------------------------------------------------------------------
Gta. Pérez Cidón 1; 3º B

 BTEQ -- Enter your SQL request or BTEQ command:
select _latin'José se comprò un camiôn';

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

'José se comprò un camiôn'
------------------------------------------------------------------------
José se comprò un camiôn

 BTEQ -- Enter your SQL request or BTEQ command:
select _latin'Gta. Pérez Cidón 1; 3º B';

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

'Gta. Pérez Cidón 1; 3º B'
---------------------------------------------------------------------------------
Gta. Pérez Cidón 1; 3º B

-David

Enthusiast

Re: Equivalent sentence in Teradata for the Oracle "CONVERT"?

Thanks for the answers.



David, I think you didn't understand what I want to do. I have a text parser in Oracle that clean and translate arrays of characters, and I want to get the same in Teradata cos I'll have to do a migration of code.

So that:

  • if my input is 'José se comprò un camiôn', I obtain 'Jose se compro un camion' (as you can see, accents are cleared)
  • if my input is 'Gta. Pérez Cidón 1; 3º B', I obtain 'Gta. Perez Cidon 1; 3? B' (and here, bad utf-8 conversions are fixed, appart from the "º", that is translated to a "?")

So my question is if there is something in Teradata that allows to detect bad conversions, code-decode and make supression of accents without ussing a translation letter by letter.



Regards

Juan
Teradata Employee

Re: Equivalent sentence in Teradata for the Oracle "CONVERT"?

If I understand correctly, you want the Teradata equivalent of the Oracle CONVERT function. For starters, I was pointing out some of the differences in string literals between the two products. There is plenty of information on the developer exchange on Oracle migations, for example: http://developer.teradata.com/database/training/oracle-to-teradata-101.

As Ulrich noted, TRANSLATE is not the equivalent of CONVERT. I say 'not' because you want to do a best-fit mapping of UTF8 to 7-bit ASCII. TRANSLATE will return a conversion exception if the source character is not in the destination repertoire. It does not do best-fit.

The best-fit mapping of UTF8 to 7-bit ASCII would conceivably need to map 110,000 assigned Unicode characters to 128 ASCII Characters. This is probably why Oracle discourages the use of CONVERT, see https://docs.oracle.com/cd/B28359_01/server.111/b28286/functions027.htm. The Unicode consortium also discourages best-fit conversions, see http://unicode.org/reports/tr22/tr22-3.html#Best-Fit_Mappings.

If you still want to do a best-fit mapping to 7-bit ASCII, you'll need to implement it as a Teradata UDF (User Defined Function). You could also try the TRANSLATE .... using UNICODE_TO_UNICODE_NFD to expose the base characters.

-David