Hi Jasvinder,
I think you could use a custom function here.
Something like that (typing directly in my mail client - so be prepared for
typos and other errors ;) ):
Java side:
==========
package my.package;
public class MyDerbyFunctions {
static Integer toInt(String str) {
try {
return Integer.valueOf(str);
}
catch(NumberFormatException nfe) {
return null;
}
}
}
Derby side:
===========
create function toInt(str varchar(128)) returns int language java external name
'my.package.MyDerbyFunctions.toInt' parameter style java no sql;
See:
http://wiki.apache.org/db-derby/DerbySQLroutines
http://db.apache.org/derby/docs/10.5/ref/rrefcreatefunctionstatement.html
Regard,
Sylvain
Jasvinder S. Bahra a écrit :
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;
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.
Jazz
--
[email protected]
http://www.chicoree.fr
