db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <Richard.Hille...@Sun.COM>
Subject Re: Converting a value held in a VARCHAR column into an INTEGER
Date Wed, 10 Mar 2010 17:14:24 GMT
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
>
>
>
>


Mime
View raw message