JJFord3 JJFord3 - 1 month ago 11
SQL Question

Removing a trailing period from improperly joined Teradata column

I have a Teradata table that I've inherited that was not formatted in a great manner.

ID
123456789.
234567890.


I've tried:

TRIM(new_card_srgt_id (FORMAT 'Z(17)9'))


but my version of Teradata gave a funny error:

Format string has combination of numeric, character, and GRAPHIC values.


Any suggestions welcomed.

UPDATE: The suggestion to use TRIM(trailing '.' from ID) results in a numeric overflow when I went to cast it. Any other way to fix it.

Answer

When you load data from a decimal field into a character field without specify the format, teradata does the type conversion, it inherently adds '.' to the trailing value.

If ID field is char and it already contains '.' and you want to extract it as decimal without the . then it is as follows

SELECT cast(id as decimal(15,0)) form table;

If ID field is decimal and you want to extract it as character without the '.' then the syntax is as follows

SELECT TRIM(ID (FORMAT 'ZZZZZZZZZZZZZ9')) from table;