Inserting space as value from a char column to varchar column

Database

Inserting space as value from a char column to varchar column

Hi All,

I have a case where-in i need to insert space as a value for a column which is declared as char from 1 table to another column in another table which is declared as varchar.

When I try to do it , I am not getting the expected value in my final table.

I am getting character_length in my final table as 0 meaning space hasn't got inserted properly.

please let me know if any of you ways of doing this in teradata.

Thanks.
2 REPLIES
Teradata Employee

Re: Inserting space as value from a char column to varchar column

Hi,

Following is a sample SQL:

CREATE VOLATILE TABLE Table1(Col1 CHAR(10)) ON COMMIT PRESERVE ROWS;
CREATE VOLATILE TABLE Table2(Col1 VARCHAR(10)) ON COMMIT PRESERVE ROWS;

INSERT Table1 (' ');
INSERT Table1 (' 1 ');
INSERT Table1 (' 2 ');
INSERT Table1 (' 3 ');
INSERT Table1 (' 4 ');
INSERT Table1 (' 5 ');

INSERT Table2 SELECT * FROM Table1;

SELECT Col1, CHARACTER_LENGTH(Col1) FROM Table2 ORDER BY 1;
SELECT Col1, CHARACTER_LENGTH(Col1) FROM Table1 ORDER BY 1;

The 1st INSERT has 2 spaces and rest have the same number of spaces as in the middle .... e.g. for 3 we have 3 spaces then 3 as digit and then again 3 spaces!

Following is the result of 1st SELECT:
Col1 Characters(Col1)
10
5 10
4 10
3 10
2 10
1 10

And following is the result of 2nd SELECT:

Col1 Characters(Col1)
0
5 6
4 5
3 4
2 3
1 2

This means what it does is, remove extra padded spaces from CHAR when inserting to VARCHAR .... which means if the column is nothing but spaces, nothing will be left of it.

What you can do is to modify SELECT as follows:

SELECT
CAST(Col1 || 'a' AS VARCHAR(11)) AS C1,
CHARACTER_LENGTH(CAST(Col1 || 'a' AS VARCHAR(11))) AS C2,
SUBSTR(CAST(Col1 || 'a' AS VARCHAR(11)), 0, C2) as COL_TO_INSERT,
CHARACTER_LENGTH(COL_TO_INSERT)
FROM
Table1;

HTH!

Regards,

MAC

Re: Inserting space as value from a char column to varchar column

Teradata implicitly trims trailing spaces when converting char(n) values to varchar(n). Assuming that this is only a problem when the source value is all spaces, you can use a case expression like the following to get the result you want:

case Col1
when ' '
then cast(' ' as varchar(10))
else cast(Col1 as varchar(10))
end

This works because the implicit type of the literal ' ' is varchar, so a conversion is avoided in that case.