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.
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