joshi123 joshi123 - 1 month ago 16
SQL Question

db2 concatenated rows displaying whitespace

I have a column in a DB2 table where the rows of data consist of two strings,

string1
and
string2
. These have a number of whitespace characters in between them, which I'm trying to remove.

When I run the following, I see the expected result, which is
string1
string2
.


SELECT REPLACE(COLUMN,' ','') FROM Source_Table;


However when I try to insert the cleansed rows into a new table, the data still contains whitespace between
string1
and
string2
.


INSERT INTO Target_Table (Column)

SELECT REPLACE(Column,' ','') FROM Source_Table;

Answer

You probably declared your columns as CHAR and DB2 is filling unused space in your column with spaces. That's by design.

If you want avoid this you should declare them as VARCHAR

Also in your SELECT you should use the TRIM or RTRIM function for removing the withespaces instead of REPLACE()