Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2009
    Posts
    3

    Unanswered: Character to Integer conversion in where clause

    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

  2. #2
    Join Date
    Jul 2006
    Location
    Pune , India
    Posts
    433
    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 ?
    Rahul Singh
    Certified DB2 9 DBA / Application Developer

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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?

  4. #4
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    Quote Originally Posted by nkamalkishore View Post
    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

    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

  5. #5
    Join Date
    Nov 2009
    Location
    Bangalore
    Posts
    25
    You can also achieve the same using CAST operator

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

    Regards,
    Sawan Gupta

  6. #6
    Join Date
    Jul 2009
    Posts
    150
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •