1

I have a number field with lenght of 10 bytes. If i store this lenght into another field that is of datatype VarChar2. How do I calculate the lenght of VarChar2?.

Some state that I can just declare it as VarChar2(MAX) as it does not matter - saying that if I was to store the word '999' into VarChar(100) and VarChar(1000) the same amount of space would be allocated.

What is the correct way to go about this?

jason bourne
  • 87
  • 1
  • 9
  • A number field with a length of 10 bytes, or 10 digits? What's the exact data type? – David Aldridge Sep 28 '15 at 13:07
  • 1
    So if you have `1000` as `number`, you need to store its length ie `4` in a `varchar2`? – Utsav Sep 28 '15 at 13:08
  • That is what i am asking. If i have a number e.g. 1000. What is the lenght if it was a varchar2? Also if it was a double, e.g. 1000.1 I would require a varchar2 with lenght of 6? – jason bourne Sep 28 '15 at 13:10
  • Use `LENGTH(yournumberColumn)` – Hotdin Gurning Sep 28 '15 at 13:12
  • if you are saving 1000 as varchar2 then its length would be 4. – Utsav Sep 28 '15 at 13:12
  • Thank you. And if you store a double, I assume I need to allocate space for the decimal point too? – jason bourne Sep 28 '15 at 13:13
  • If you have column or variable with number(10) it can hold from 0 to 9999999999 so better you can create a column with varchar2(10) it will hold ten characters. – Tharunkumar Reddy Sep 28 '15 at 13:13
  • You may want to read here: http://stackoverflow.com/questions/13858562/why-using-anything-else-but-varchar24000-to-store-strings-in-an-oracle-databas it has some useful supporting info ... – Ditto Sep 28 '15 at 13:14
  • A "double" is an internal representation of a number. What you are asking about is storing a string that represents a number, and you'd need one byte per character (including any leading minus sign, decimal point, or trailing decimals) – David Aldridge Sep 28 '15 at 13:16

2 Answers2

1

It makes no difference in terms of storage used whether you save a particular string in a VarChar2(100) or a VarChar2(1000) -- the length just acts as a constraint on the number of bytes (or characters) that can be stored.

The Oracle NUMBER data type does not use 4 bytes to save 1000, though. You can check the storage space by using:

select vsize(1000) from dual;

... and compare with ...

select vsize('1000') from dual;

The storage requirement for a number is more dependent on the quantity of significant figures stored, so 1000 or 1000000 require fewer bytes than 1234 or 1234567.

David Aldridge
  • 51,479
  • 8
  • 68
  • 96
0

If your datatype is NUMBER(10) then it can store max 9999999999 and min -9999999999

If you want to store this data to a varchar column then varchar(11) would be enough.

Praveen
  • 8,945
  • 4
  • 31
  • 49