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
