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.

Poor table design. Why do you have mixed data types? – jarlh Sep 08 '16 at 09:02 — jarlh, Sep 08 '16 at 09:02

score 3 · Accepted Answer · answered Sep 08 '16 at 09:15

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

Convert char to int TeraData Sql

1 Answers1

Linked