db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Jasvinder S. Bahra" <bbdl21...@blueyonder.co.uk>
Subject Converting a value held in a VARCHAR column into an INTEGER
Date Wed, 10 Mar 2010 15:12:29 GMT

I'm using Apache Derby version 10.5.3.

I have a table with a column of type VARCHAR...

    CREATE TABLE db.card (
        name     VARCHAR(64)    NOT NULL,
        power    VARCHAR(16)    NOT NULL

The column predominantly holds numeric data, but I made the column type
VARACHAR because there is an occasional value that has non-numeric character
in it (usually an asterix (*), an open brace ({) or a hat character(^)).

I would like to perform a query which returns those values where the column
value is greater than 7.

I tried doing "SELECT name, power FROM db.card WHERE INTEGER(power) > 7",
but this returned an error...

   ERROR 22018: Invalid character string format for type INTEGER.

I also tried "SELECT power FROM db.card WHERE CAST(power AS INTEGER) > 7",
but this returned the same error.

Does anyone have any ideas on how I can go about this?



View raw message