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 Re: Converting a value held in a VARCHAR column into an INTEGER
Date Thu, 11 Mar 2010 12:23:56 GMT
> You may be able to use a CASE expression to solve this problem:
> SELECT name, power FROM card
> ( case when power = '*' or power = '{' or power = '^' then null else 
> integer( power ) end ) > 7;

Unfortunately, the non-numeric characters I mentioned are the only ones in 
the data set *at the moment*.  In the future, other non-numeric characters 
may be introduced - which makes this technique inpractical.

I think i'm going to have to bite the bullet and just store the data in two 
columns - one of type INTEGER, the other VARCHAR, and just make sure the 
INSERT logic only populates the INTEGER column if the value is made up of 
numeric characters.

I know this is a bit of a no-no in terms of database design, but I can't see 
any way around it.

In any case, thanks for the suggestion Rick.


View raw message