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?

Various ideas, one of which is both useable and simple. Create a function to use when you do the compare (perhaps in a package), something like

	function my_to_number
	( p varchar2 
	, the_default number default null
	) return number
	is
	begin
		return to_number( p );
	exception
	when LOOK_UP_ERROR_NAME then return the_default;
	when others             then raise;
	end;

Now use that when doing the comparison, using a default that would make sense for the comparison. Many variations are possible, I won't suggest any because I think they should be obvious enough if that is not quite what you need.

$0.10 Received on Tue Jan 26 2010 - 17:04:11 CST