Re: Dumb Character Column

From: Robert Klemme <>
Date: Wed, 27 Jan 2010 00:43:27 +0100
Message-ID: <>

On 26.01.2010 23:50, The Magnet wrote:
> 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 could either make sure you get only numeric text data (i.e. select appropriately) or use a CASE expression to convert valid numeric data into numbers an everything else into, say, -178. In determining whether a value is valid number you could use regular expressions or LIKE.

Kind regards


remember.guy do |as, often| as.you_can - without end
Received on Tue Jan 26 2010 - 17:43:27 CST

Original text of this message