Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2016
    Posts
    2

    Unanswered: numbers stored as char

    Hi all.

    I have a db where numeric values are stored as char - numbers can be larger than an integer ( although int8 might work ) - I can't change that.

    is it possible to create a query that works with arithmetic on these character records ( maybe using CAST within the query ? )
    EG - in basic form :
    Code:
    select * from <XYZ> where bignum > '1999999'
    without producing results for 2,20,200,3,30,300 etc

    Actually I think I answered my own question . . . using cast ....

    EG
    Code:
    select cast (bignum as int) from <XYZ> where cast(bignum as int) > '1999999'
    Last edited by Tikiman; 11-03-16 at 09:44. Reason: i think i found the answer

  2. #2
    Join Date
    Sep 2011
    Location
    Pont l'Abbé, Brittany, France
    Posts
    394
    Provided Answers: 1
    I would suggest to use cast ;-)

    watch out with cast used in the predicate: it will disable the use of an index if there is an index on bignum.
    In that case, you want to create an index on cast(bignum). You will have to create a function that returns cast(bignum) and create the index on that function

  3. #3
    Join Date
    Nov 2016
    Posts
    2
    Many thanks for the advice...


  4. #4
    Join Date
    Sep 2011
    Location
    Pont l'Abbé, Brittany, France
    Posts
    394
    Provided Answers: 1
    To create a functional index, check this post
    https://www.ibm.com/developerworks/d...dm-0712wilcox/

    It may give you great ideas for the future
    Cheers
    Eric

Posting Permissions

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