Re: Dumb Character Column

> 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