, , , , , , , , , , , ,

Some DB2 columns defined as CHAR or VARCHAR can eventually store both character data and numeric data. Some of these might be just numeric data all together. Now because of loading data into this table from a csv file, there are chances that “all numeric” fields loaded into these columns are loaded as right justified. When these should have been loaded as left justified. To rectify this, we need to first identify the rows that have “all numeric” data in this fields and then update them to be left justified.

To identify if the CHAR or VARCHAR field (col1) has “all numeric” data, below check can be done.

1) TRANSLATE(col1, ‘X ‘,’ 0123456789′) = ‘ ‘

2) UCASE(col1) = LCASE(col1) —-> both will be same only for numeric

3) col1 LIKE ‘ %’ —-> As numeric data is right justified, this can be checked.