I have a column with datatype char(3) but holds value like '1' or null .
I need to load the data to another table with column having datatype char(1). Please help me how to cast it to load it into char(1) column.
You could just use:
CAST(col-name as CHAR(1))
Or you can just copy the column across, TD will automatically truncate the data value
- but only if you're running in Teradata mode. If you're running in ANSI mode and truncate data it will fail.
A (possibly) safer option and one which (in my opinion) is slightly better because your code 'documents' what it is doing is:
SUBSTRING(col-name FROM 1 for 1)
CAST(col-name as CHAR(1)) doesnt work and SUBSTRING(col-name FROM 1 for 1) also doesnt work .
SUBSTRING(col-name FROM 3 for 1) this works.
I'm glad that you got something to work.
Can I ask what do you mean "CAST(col-name as CHAR(1)) doesn't work"? Does this give you an error? Does it not produce the correct results? Same questions for the other SUBSTRING option that I provided.
It may be that they didn't produce the correct results (which is obviously not good).
What I've realised looking at my answers is that I assumed that the 1 character that you wanted extract was in the first position of the 3 character column.
So assuming that you wanted '1' out of '1 ' then my original suggestions would work.
However if you wanted '1' out of ' 1' then my original suggestions would not work and why your new code 'works'.
if that is what is happening then providing that the '1' will always be in the third position your code is fine.However if the data might ' 1 ' or '1 ' then your new code will not find the character.
- If this is the case then I'd use "SUBSTRING(TRIM(col-name) FROM 1 FOR 1)"
seems like '1' is in 3 position but i am not sure even if i trim like "SUBSTRING(TRIM(col-name) FROM 1 FOR 1)"
it still gives me blank record. Please let me know your views
I'm guessing then that you have some non-blank data before the character that you're expecting.
I tried the following:
CREATE SET VOLATILE TABLE vt1 (col1 INTEGER ,col2 CHAR(3) ) ON COMMIT PRESERVE ROWS;
INSERT INTO vt1 VALUES(1,'1 ');
INSERT INTO vt1 VALUES(2,' 2');
INSERT INTO vt1 VALUES(3,' 3 ');
I then ran the following
SELECT a.* ,SUBSTRING(TRIM(col2) FROM 1 FOR 1) AS final_col FROM vt1 AS a ORDER BY 1;
My results are:
col1 col2 final_col 1 1 1 2 2 2 3 3 3
Note that I get data in 'final_col' for all three rows.
What is different in your data or sql?
which version of Teradata you are using, because i am able to execute SEL CAST(COL as CHAR(1) successfully in V15.