Dumb Character Column
in [Oracle]
|
Prev: lag lag lag
Next: Oracle 11.2 for iPad
From: Tim X on 27 Jan 2010 03:22 The Magnet <art(a)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. 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 -- tcross (at) rapttech dot com dot au
From: Shakespeare on 27 Jan 2010 03:51 Op 27-1-2010 9:22, Tim X schreef: > The Magnet<art(a)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
From: The Magnet on 27 Jan 2010 11:31 On Jan 27, 2:51 am, Shakespeare <what...(a)xs4all.nl> wrote: > Op 27-1-2010 9:22, Tim X schreef: > > > > > The Magnet<a...(a)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;
From: Robert Klemme on 27 Jan 2010 14:33 On 27.01.2010 17:31, The Magnet wrote: > 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: If you only need numbers and there is a significant amount of non numbers you could also return NULL for non numbers in order to keep the index small. Cheers robert -- remember.guy do |as, often| as.you_can - without end http://blog.rubybestpractices.com/
From: Shakespeare on 27 Jan 2010 14:45
Op 27-1-2010 17:31, The Magnet schreef: > On Jan 27, 2:51 am, Shakespeare<what...(a)xs4all.nl> wrote: >> Op 27-1-2010 9:22, Tim X schreef: >> >> >> >>> The Magnet<a...(a)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 |