Dumb Character Column
in [Oracle]
|
Prev: lag lag lag
Next: Oracle 11.2 for iPad
From: The Magnet on 26 Jan 2010 17:50 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? Thanks!
From: Robert Klemme on 26 Jan 2010 18:43 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/
From: Malcolm Dew-Jones on 26 Jan 2010 18:04 The Magnet (art(a)unsu.com) wrote: : 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
From: hpuxrac on 26 Jan 2010 21:16 On Jan 26, 5:50 pm, The Magnet <a...(a)unsu.com> wrote: snip > 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? > > Thanks! You could do an order by to sort the character field generate some rownums and then select out just the rownums to create a number for that field. Won't scale very well if you have many rows in the table ...
From: Shakespeare on 27 Jan 2010 02:11
Op 26-1-2010 23:50, The Magnet schreef: > > 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? > > Thanks! What version of Oracle (Database)? Shakespeare |