Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Discussion" <derby-user@db.apache.org>
Received-SPF: pass (nike.apache.org: domain of victorianladyturtle@gmail.com
 designates 209.85.216.203 as permitted sender)
DomainKey-Signature: a=rsa-sha1; c=nofws;
        d=gmail.com; s=gamma;
        h=return-receipt-to:reply-to:from:to:references:in-reply-to:subject
         :date:organization:message-id:mime-version:content-type
         :content-transfer-encoding:x-mailer:thread-index:content-language
         :disposition-notification-to;
        b=cJUZCXBbEmzFMsrxq1axNnJ5r7wP0HqnNtsdNwTLfTbX3BLX6D3aarRfbv3tOPwiCt
         IQBVhcddXV2xaE+qOtlIFNXkIof43MXm5Hqps/IIA0CIAMTiujLNJq+sGxi47VqxJDgy
         9oxMz03rec/jNizCXpwTNF8/pxC8FLagcpbp8=
Reply-To: <annetta@nettasoftonline.com>
From: "Annetta C Green" <victorianladyturtle@gmail.com>
To: "'Derby Discussion'" <derby-user@db.apache.org>
References: <58C9501833EA44618C0651C5FA9FF878@atlantis>
 <4B97D370.5030401@sun.com> <49EF399C79174BF099417BFABBCB929B@atlantis>
 <4B98F41F.9090605@sun.com> <4B98F7EE.6030805@chicoree.fr>
In-Reply-To: <4B98F7EE.6030805@chicoree.fr>
Subject: RE: Converting a value held in a VARCHAR column into an INTEGER
Date: Fri, 12 Mar 2010 00:51:27 -0800
Organization: NettaSoft
Message-ID: 
 <!&!AAAAAAAAAAAYAAAAAAAAAGswT5KUBLdFstjqVmA6oRhCgwAAEAAAABmh37FdpHFIhckXIL9FYGQBAAAAAA==@gmail.com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Thread-Index: AcrBSNugUKupoidnS02YAapL8np3KAAdVsYg
Content-Language: en-us

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 =3D 0;
    for (int x =3D 0; x < 10; x++)=20
    {
         if(str.startsWith(String.valueOf(x))
          {
              myInteger =3D 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]=20
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"=20
modifier for the static method), here is a complete transcription for =
the=20
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=3D.:$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=3Dtrue';
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=20

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

2 rows selected
ij>


Rick Hillegas a =E9crit :
>=20
> Hi Jazz,
>=20
> Some more comments inline...
>=20
> 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 =3D '*' or power =3D '{' or power =3D '^' then =
null else=20
>>> integer( power ) end ) > 7;
>>
>> Unfortunately, the non-numeric characters I mentioned are the only=20
>> ones in the data set *at the moment*.  In the future, other=20
>> non-numeric characters may be introduced - which makes this technique =

>> inpractical.
> Another solution would be to write a function which returns null for=20
> your special characters and returns an integer for values which really =

> are numbers. As you add more non-numeric characters, you just have to=20
> adjust this function. Then your query would look like this:
>=20
> 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=20
>> in two columns - one of type INTEGER, the other VARCHAR, and just =
make=20
>> sure the INSERT logic only populates the INTEGER column if the value=20
>> is made up of numeric characters.
> If you pursue this approach, you are still going to have to maintain =
the=20
> triaging logic which separates numbers from non-numeric strings. You=20
> could put the triaging logic in a function which is invoked at INSERT=20
> time and then add a generated column to your table:
>=20
> CREATE TABLE card (
>       name     VARCHAR(64)    NOT NULL,
>       power    VARCHAR(16)    NOT NULL,
>       integerPower int generated always as ( myCastFunction( power ) )
>   );
>=20
> The advantage of this approach is that you can put a useful index on=20
> integerPower and that might speed up your queries.
>=20
> Hope this helps,
> -Rick
>>
>> I know this is a bit of a no-no in terms of database design, but I=20
>> can't see any way around it.
>>
>> In any case, thanks for the suggestion Rick.
>>
>> Jazz
>>
>>
>=20
>=20
>=20


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