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 14:02:22 GMT
Hi again Jazz,
Hi Rick,

Since there was at least one typo in my previous message (I forgot the "public" 
modifier for the static method), here is a complete transcription for the 
function-based solution:

#
# Java source code:
#
sh$ cat MyFunctions.java
public class MyFunctions {
   public static Integer toInt(String str) {
     try {
       return Integer.valueOf(str);
     }
     catch(NumberFormatException nfe) {
       return null;
     }
   }
}

sh$ javac MyFunctions.java

#
# Demo in ij:
#
sh$ export CLASSPATH=.:$DERBY_HOME/lib/derby.jar:$DERBY_HOME/lib/derbytools.jar
sh$ java org.apache.derby.tools.ij
ij version 10.5
ij> CONNECT 'jdbc:derby:dummy;create=true';
ij> CREATE TABLE TBL(value VARCHAR(255));
ij> INSERT INTO TBL(value) VALUES('1'), ('7'), ('*'), ('10'), ('{'), ('99');
6 rows inserted/updated/deleted
ij> CREATE FUNCTION toInt(str varchar(255))
               RETURNS int
               PARAMETER STYLE JAVA
               NO SQL
               LANGUAGE JAVA
               EXTERNAL NAME 'MyFunctions.toInt';
0 rows inserted/updated/deleted
ij> SELECT toInt(value) FROM TBL;
1
-----------
1
7
NULL
10
NULL
99

6 rows selected
ij> SELECT value FROM tbl WHERE toInt(value)>7;
VALUE 

--------------
10
99

2 rows selected
ij>


Rick Hillegas a écrit :
> 
> 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
>>> 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.
> 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:
> 
> CREATE TABLE card (
>       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,
> -Rick
>>
>> 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