#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    5
    Rep Power
    0

    MySQL query with CONV function


    table i used to execute the query is
    _Count _FFID _VLD
    -------------------------
    1 0 1
    2 3 1
    3 1 1
    4 2 1
    5 e 1

    *********SELECT _FFID FROM CsFFPCBValid WHERE CONV(_FFID,16,10) > 12

    When i executed the query given above i got the result as

    _FFID
    ------
    e

    But when i executed the query below , i got different result even though these have same meaning

    *************SELECT _FFID FROM CsFFPCBValid WHERE CONV(_FFID,16,10) > CONV('c',16,10)

    _FFID
    ------
    3
    2
    e

    Why it is so?

    I am using mysql5.5.27 -win 32 version and Navicat as gui
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2003
    Location
    Paris Uppland
    Posts
    2,385
    Rep Power
    391
    conv returns a character value. The second comparsion will be between strings and the string '2' is greater than the string '12'.

    In the first comparison, the result of conv will be implicitly casted as integer, thus comparing 2 > 12 which evaluates to false.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    5
    Rep Power
    0
    SELECT _FFID FROM CsFFPCBValid WHERE CONV(_FFID,16,16) > 0xc

    gives the result
    _FFID
    ------
    0
    3
    1
    2
    e


    this implicit casting is only in the case of integers?
    How can i compare character value and hexadecimal value?
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2003
    Location
    Paris Uppland
    Posts
    2,385
    Rep Power
    391
    Code:
    select _FFID
      from CsFFPCBValid
     where cast(CONV(_FFID,16,10) as signed) > cast(CONV('c',16,10) as signed)
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    5
    Rep Power
    0
    thanks..

IMN logo majestic logo threadwatch logo seochat tools logo