Converting a value held in a VARCHAR column into an INTEGER

[Jasvinder S. Bahra]

Hi,

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?

Regards,

Jazz