Wednesday, December 28, 2016

DB2 Compare numeric values in a char column

Consider the following scenario. You have a column which is defined as CHAR but contains numeric values. Your task is to select the rows which has values greater than an integer constant.
Here is how you can compare text values to integer in DB2

T1 is the table with a CHAR(2) column C1

SELECT * FROM T1;
---------+---------+-
C1                   
---------+---------+-
6
--
                    
 6                   
30                   
1                   
 1                

Do note that the column has null (2nd row) and spaces(3rd row). So we need to accommodate for that. The CAST function we use would work with NULL values, but if there are SPACES, it will throw SQL error -420.
You can achieve the objective with below query

SELECT * FROM T1 WHERE CAST(REPLACE(C1,'  ','00') AS INT) > 5;
---------+---------+---------+---------+---------+---------+--
C1                                                            
---------+---------+---------+---------+---------+---------+--
6                                                             
 6                                                        
30                                                

Note : When using replace function make sure you replace for the whole length of the Char field else you would end up with unexpected results. 

No comments:

Post a Comment