Re: Dumb Character Column

From: Shakespeare <>
Date: Wed, 27 Jan 2010 20:45:24 +0100
Message-ID: <4b6097d3$0$22938$>

Op 27-1-2010 17:31, The Magnet schreef:
> On Jan 27, 2:51 am, Shakespeare<> wrote:
>> Op 27-1-2010 9:22, Tim X schreef:
>>> The Magnet<> writes:
>>>> Hi,
>>>> Ok, whomever wrote this dumbass application should be put on trial.
>>>> Anyhow, we have a column in our table which is defined as VARCHAR2,
>>>> but contains both numbers AND character data.
>>>> Problem is that we need to do some< & > in a query on the numeric
>>>> content. But, because of the character data we get an invalid
>>>> number. If we quote the numbers it does a character compare, not a
>>>> numeric compare.
>>>> Still looking on the internet for an answer, but anyone here have any
>>>> ideas?
>>> You didn't indicate how large the data set is or what indexes you have
>>> etc. However, one thing I'd consider would be rolling my own function.
>>> for example, you could have it return a number if the varchar2 passed is
>>> all digits and return null if it is characters or mixed. Alternatively,
>>> depending on oracle version, you could use regexp to exclude rows with
>>> characters/non-digits or maybe some creative decode/case.
>>> A lot may depend on how your data is distributed and whether you can
>>> rely on some particular property. for example, do/will any of the colums
>>> start with a 0,1,2,3,4,5,6,7,8,9 and contain non digits i.e. 94FE or are
>>> all non numeric values starting with a letter? If this were the case,
>>> you could just exclude rows where the first char is not a digit or maybe
>>> all the numbers are a certain length etc.
>>> While rolling your own function is probably the easiest solution, it
>>> does have drawbacks - such as possibly screwing up the use of any
>>> indexes, so eliminating candidates without applying a function to the
>>> column will likely be faster.
>> Since the function to return a number value is determisistic, a function
>> based index can be used.
>>> You might consider, depending on how many rows are involved,
>>> pre-filtering into a global temp table or using a view etc.
>>> A lot depends on the number of rows involved, what else the select query
>>> has to do, maybe your already doing a full table scan, so concerns about
>>> indexes may not matter or using them may make it less efficient or
>>> maybe this query will only run once and efficiency is not a critical
>>> concern or perhaps it has to run every 15 minutes or perhaps ......
>>> Tim
>> Shakespeare
> We are on Oracle 10g. My solution was that of above. Do a function
> that returns a 1 or 0 and query based on that:
> SELECT min_init_invest
> FROM mfund_lookup mf, fund_daily_prices dp, fund_classes fc
> WHERE mf.ticker = dp.ticker(+)
> AND mf.ticker = fc.ticker(+)
> AND DECODE(IS_A_NUMBER(min_init_invest),1,min_init_invest,NULL)>
> 2000;

If you were on 11 you could have used a virtual column.

Actually, you don't need the index killer DECODE. If you write a function GetNumber, which returns null for non numbers (though 0 would do as well in your case, but enlarge the index) and the number value for numbers, you could just plain Getnumber(min_init_invest) in your where clause. With a function based index, the function might even not be called at all in your select, for the result value is already present in the index (which behaves as a virtual column).

Shakespeare Received on Wed Jan 27 2010 - 13:45:24 CST

Original text of this message