db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Sylvain Leroux <sylv...@chicoree.fr>
Subject Re: Converting a value held in a VARCHAR column into an INTEGER
Date Thu, 11 Mar 2010 13:34:25 GMT
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
> 
> 
> 
> 


-- 
sylvain@chicoree.fr
http://www.chicoree.fr



Mime
View raw message