Re: Dumb Character Column
From: Shakespeare <whatsin_at_xs4all.nl>
Date: Wed, 27 Jan 2010 09:51:45 +0100
Message-ID: <4b5ffe9d$0$22917$e4fe514c_at_news.xs4all.nl>
Op 27-1-2010 9:22, Tim X schreef:
> The Magnet<art_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.
Date: Wed, 27 Jan 2010 09:51:45 +0100
Message-ID: <4b5ffe9d$0$22917$e4fe514c_at_news.xs4all.nl>
Op 27-1-2010 9:22, Tim X schreef:
> The Magnet<art_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 Received on Wed Jan 27 2010 - 02:51:45 CST