cast char(3) to char(1)

Database
The Teradata Database channel includes discussions around advanced Teradata features such as high-performance parallel database technology, the optimizer, mixed workload management solutions, and other related technologies.
Enthusiast

cast char(3) to char(1)

Hi ,

 

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.

6 REPLIES
Apprentice

Re: cast char(3) to char(1)

Hi,

 

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)

Cheers,

Dave

 

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Enthusiast

Re: cast char(3) to char(1)

Hi

 

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.

 

Apprentice

Re: cast char(3) to char(1)

Hi,

 

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)"

 

Cheers,

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Enthusiast

Re: cast char(3) to char(1)

Hi Dave,

 

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

Apprentice

Re: cast char(3) to char(1)

Hi,

 

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?

Cheers,

Dave

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Enthusiast

Re: cast char(3) to char(1)

Hey Venky,

 

which version of Teradata you are using, because i am able to execute SEL CAST(COL as CHAR(1) successfully in V15.

 

thanks,

Sauhard Jain