Re: numbers in varchar2 field

On 8 Jun 2006 20:56:07 -0700, hinerman_at_gmail.com wrote:

>I have a table with a varchar2(25) field. Most of the data in there is
>a 9 digit number, while a few records have 4 digit numbers. I have it
>as a varchar2 field to allow for some instances of leading zeros. My
>problem is that the 4 digit numbers aren't behaving like strings. If I
>query the table like where fieldName = '4231', it doesn't return any
>results. But if I query where fieldName = 4231, without the quotes, it
>returns results. Is there some reason these records are being handled
>differently?

In the second case you are implicitly using to_number(fieldname) = 4231.
If your data is stored as ' 4231', it is only natural the first select fails.
You are ending up in a mess, and should convert the column to a number(n) asap. Leading zeroes is a *display* issue.

--
Sybrand Bakker, Senior Oracle DBA