Showing posts with label Oracle. Show all posts
Showing posts with label Oracle. Show all posts

Saturday, September 20, 2014

VARCHAR Data type in Oracle

VARCHAR and VARCHAR2 Datatypes in Oracle / Overview of VARCHAR Datatype / How to use VARCHAR type in Oracle to declare and use Attributes? / Oracle VARCHAR datatype How to?


VARCHAR2 and VARCHAR Datatypes


VARCHAR2 datatype is used to store Variable-length character strings.

Syntax

VARCHAR2[(size [BYTE | CHAR])]

where VARCHAR2 is the datatype,
          size is the maximum length of the string to be stored
          BYTE/CHAR would mean the same thing. That is, size in number of Bytes or Characters.

Overview
Datatype
VARCHAR2
Default Size
1 Byte/Character
Minimum Size
1 Byte/Character
Maximum Size
4000 Bytes/Characters
If the value to be stored is shorter than the declared size?
The memory needed to store the value will only be used. [not the size bytes]
If the value to be stored is longer than the declared size?
ERROR
Memory required to store 1 Character
Depends on the character set used. It may vary from 1 byte to 4 bytes. For example, if the character to be stored is English character then 1 byte, if it is Chinese character then 3 bytes
Permitted Values
Alphanumeric characters with various native character data set support


What is variable length? It uses the size number of Bytes to store any value of size length. If the value to be stored is less than size, then it occupies only the amount of memory required to store that value. For example, let us consider the declaration of variable RegNo;
Student_Name VARCHAR2(25);
For this declaration, if the value for any Student_Name is of length 25 characters then 25 bytes will be used. If any of the Student_Name value is only 10 characters then only 10 bytes will be used. The values that are to be stored and the actual storage along with the total amount of memory consumed are given in the table below for reference;

Declaration
Actual Data
Stored Data
Value to be stored
Bytes needed
Data Stored
Bytes used
Bytes saved
VARCHAR2(5)
‘R101’
4
‘R101‘
4
1
VARCHAR2(5)
‘R1001’
5
‘R1001’
5
0
VARCHAR2(5)
‘R1’
2
‘R1‘
2
3
VARCHAR2(10)
‘ID1456’
6
‘ID1456‘
6
4
VARCHAR2(10)
‘ID’
2
‘ID‘
2
8

Note: Preceding blank spaces and intermediate blank spaces in an input will be considered as data. Only trailing blank spaces gets removed (eliminated) automatically.



 

CHAR Data type in Oracle

CHAR Datatype in Oracle / Overview of CHAR Datatype / How to use CHAR type in Oracle to declare and use Attributes? / Oracle CHAR datatype How to?


CHAR Datatype


CHAR datatype is used to store Fixed-length character strings.

Syntax

CHAR[(size[BYTE | CHAR])]

where CHAR is the datatype,
          size is the maximum length of the string to be stored
          BYTE/CHAR would mean the same thing. That is, size in number of Bytes or Characters.


Overview
Datatype
CHAR
Default Size
1 Byte/Character
Minimum Size
1 Byte/Character
Maximum Size
2000 Bytes/Characters
If the value to be stored is shorter than the declared size?
Blank-padded to the actual size declared
If the value to be stored is longer than the declared size?
ERROR
Memory required to store 1 Character
Depends on the character set used. It may vary from 1 byte to 4 bytes.
Permitted Values
Alphanumeric characters with various native character data set support

 What is fixed length? It uses the size number of Bytes to store any value irrespective of the length of the stored values. That is, if the actual value to be stored is less than the size declared, the actual value gets padded with blank spaces to count the size. For example, let us consider the declaration of variable RegNo;
RegNo CHAR(5);
For this declaration, the values that are to be stored and the actual storage along with the total amount of memory consumed is given in the table below;

Declaration
Actual Data
Stored Data
Value to be stored
Bytes needed
Data Stored
Number of Blank-spaces padded
Bytes used
CHAR(5)
‘R101’
4
‘R101 ‘
1
5
CHAR(5)
‘R1001’
5
‘R1001’
0
5
CHAR(5)
‘R1’
2
‘R1  
3
5
CHAR(10)
‘ID1456’
6
‘ID1456   
4
10
CHAR(10)
‘ID’
2
‘ID       
8
10



Note: Preceding blank spaces and intermediate blank spaces in an input will be considered as data.That is, for storing a value ' R101', where this value start with a blank space, we need 5 bytes.


Character based data types - Content

Featured Content

Multiple choice questions in Natural Language Processing Home

MCQ in Natural Language Processing, Quiz questions with answers in NLP, Top interview questions in NLP with answers Multiple Choice Que...

All time most popular contents

data recovery