Re: Dumb Character Column
From: The Magnet <art_at_unsu.com>
Date: Wed, 27 Jan 2010 08:31:41 -0800 (PST)
Message-ID: <85a25d36-034c-4647-9ed7-5543d1dd1fe7_at_o28g2000yqh.googlegroups.com>
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
Date: Wed, 27 Jan 2010 08:31:41 -0800 (PST)
Message-ID: <85a25d36-034c-4647-9ed7-5543d1dd1fe7_at_o28g2000yqh.googlegroups.com>
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;
Received on Wed Jan 27 2010 - 10:31:41 CST