Re: Converting a value held in a VARCHAR column into an INTEGER

[Rick Hillegas]

Hi Jazz,

You may be able to use a CASE expression to solve this problem:
SELECT name, power FROM card
WHERE
( case when power = '*' or power = '{' or power = '^' then null else 
integer( power ) end ) > 7
;

Hope this helps,
-Rick

Jasvinder S. Bahra wrote:
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