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 Thu, 11 Mar 2010 13:46:07 GMT
Hi Jazz,

Some more comments inline...

Jasvinder S. Bahra wrote:
>> 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.
Another solution would be to write a function which returns null for 
your special characters and returns an integer for values which really 
are numbers. As you add more non-numeric characters, you just have to 
adjust this function. Then your query would look like this:

select name, power from card
where myCastFunction( power ) > 7;
> 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.
If you pursue this approach, you are still going to have to maintain the 
triaging logic which separates numbers from non-numeric strings. You 
could put the triaging logic in a function which is invoked at INSERT 
time and then add a generated column to your table:

       name     VARCHAR(64)    NOT NULL,
       power    VARCHAR(16)    NOT NULL,
       integerPower int generated always as ( myCastFunction( power ) )

The advantage of this approach is that you can put a useful index on 
integerPower and that might speed up your queries.

Hope this helps,
> 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.
> Jazz

View raw message