Re: Dumb Character Column
From: Robert Klemme <shortcutter_at_googlemail.com>
Date: Wed, 27 Jan 2010 00:43:27 +0100
Message-ID: <7s9d14Frb8U1_at_mid.individual.net>
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?
Date: Wed, 27 Jan 2010 00:43:27 +0100
Message-ID: <7s9d14Frb8U1_at_mid.individual.net>
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.
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions129.htm#i1239887
Kind regards
robert
-- remember.guy do |as, often| as.you_can - without end http://blog.rubybestpractices.com/Received on Tue Jan 26 2010 - 17:43:27 CST