Character to Integer conversion in where clause

nkamalkishore · #1 (**permalink**) 11-13-09, 06:01

Hello All,

How to convert a character column to integer in the where clause.

Example:

select * from tabname where int(colname) between 1 and 50;

In the above example the int(colname) is not working but it works if specified as select int(colname) from tabname.

Thanks in advance
Kamal K Nelli

rahul_s80 · #2 (**permalink**) 11-13-09, 06:55

if this works : select int(colname) from tabname
then it will surely work for : where int(colname) between 1 and 50

which db2 version you are using ? whats the error code ?

tonkuma · #3 (**permalink**) 11-13-09, 06:57

What is the meaning of "not working"?

Did you got error messages?
or, the result was different from what you expected?

What DB2 version/release?
What platform(OS) are you using?

DBFinder · #4 (**permalink**) 11-13-09, 19:42

Quote:

Originally Posted by nkamalkishore

In the above example the int(colname) is not working but it works if specified as select int(colname) from tabname.

It works on my system DB2 V9.5.0 on Win-XP

Quote:

C:\Program Files\IBM\SQLLIB\BIN>db2 select name,age from tst

NAME AGE
---------- ---
Sarbjit 16
Surjit 11
Aman 05
Gauri 25
Harsh 52

5 record(s) selected.

C:\Program Files\IBM\SQLLIB\BIN>db2 select name,age from tst where int(age) between 10 and 30

NAME AGE
---------- ---
Sarbjit 16
Surjit 11
Gauri 25

3 record(s) selected.

C:\Program Files\IBM\SQLLIB\BIN>db2level
DB21085I Instance "DB2" uses "32" bits and DB2 code release "SQL09050" with
level identifier "03010107".
Informational tokens are "DB2 v9.5.0.808", "s071001", "NT3295", and Fix Pack
"0".
Product is installed at "C:\PROGRA~1\IBM\SQLLIB" with DB2 Copy Name "DB2COPY1".

Let me know if you need me test on different version. Logically it should work.

DBFinder

sawangupta · #5 (**permalink**) 11-15-09, 06:18

You can also achieve the same using CAST operator

CAST specification - IBM DB2 9.7 for Linux, UNIX, and Windows

Regards,
Sawan Gupta

DB2Plus · #6 (**permalink**) 11-15-09, 11:03

It means result table is empty on select, but select shown some integers in between.

But how I understand he shown to us part of predicate....

Kara.

Internet Services

Web Development

Digital Marketing

Consumer Tech