db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Annetta C Green" <victorianladytur...@gmail.com>
Subject RE: Converting a value held in a VARCHAR column into an INTEGER
Date Fri, 12 Mar 2010 08:51:27 GMT
Hi,

Since everything is being brought in as a VARCHAR, is there any reason you
cannot use the .startsWith(") method from the String class?

 public static int convertToInt(String str)
 {
    int myInteger = 0;
    for (int x = 0; x < 10; x++) 
    {
         if(str.startsWith(String.valueOf(x))
          {
              myInteger = Integer.parseInt(str);
              break;
          }//end if
     }//end for
     return myInteger;
 }//end convertToInt


Respectfully,

Annetta C. Green
Senior Developer
NettaSoft
Email: annetta@nettasoftonline.com
Website: http://www.nettasoftonline.com



-----Original Message-----
From: Sylvain Leroux [mailto:sylvain@chicoree.fr] 
Sent: Thursday, March 11, 2010 6:02 AM
To: Derby Discussion
Subject: Re: Converting a value held in a VARCHAR column into an INTEGER

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