Convert char to int TeraData Sql

Question

i'm trying to convert a column from char (8) to integer in order to make a referential integrity with an integer. IT didn't work and I test a select in order to check the cast. Utente_cd is a char(8) column

SEL 
CAST(UTENTE_CD AS INTEGER) 
FROM TABLEA

Teradata produces this error :

SELECT Failed. 2621: Bad character in format or data of TABLEA.

Sometime the char column contains also an alphanumeric code that I should discard.

Thank you

Answer 1

In TD15.10 you can do TRYCAST(UTENTE_CD AS INTEGER) which will return a NULL instead of failing.

Before there are multiple ways:

-- TO_NUMBER returns NULL when failing
CAST(TO_NUMBER(UTENTE_CD) AS INTEGER)

-- check if there are only digits
CASE WHEN UTENTE_CD  = ''                     -- all spaces
       THEN NULL
     WHEN LTRIM(UTENTE_CD, '0123456789') = '' -- only digits
       THEN CAST(UTENTE_CD AS INTEGER)
     ELSE NULL
END