Re: Dumb Character Column

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

Op 27-1-2010 17:31, The Magnet schreef:
> On Jan 27, 2:51 am, Shakespeare<what..._at_xs4all.nl> wrote:
>> Op 27-1-2010 9:22, Tim X schreef:
>>
>>
>>
>>> The Magnet<a..._at_unsu.com> 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