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
--
[email protected]
http://www.chicoree.fr
