Oracle PL/SQL Tutorial - PL/SQL CHAR VARCHAR2

The CHAR data types store fixed-length character strings.

The VARCHAR2 data types store variable-length character strings.

All string literals have data type CHAR.

The syntax for specifying a CHAR or VARCHAR2 data item is:


[ CHAR | VARCHAR2 ] [( maximum_size [ CHAR | BYTE ] )]

For example:


CHAR 
VARCHAR2 
CHAR(10 CHAR) 
VARCHAR2(32 BYTE)

The maximum_size must be an integer literal in the range 1..32767.

The default value is one.

Comparing Character Values

In the following example shows how to compare two VARCHAR2 value.


DECLARE --  ww w.j a  v  a  2 s  .  co m
  last_name1 VARCHAR2(10) := 'HTML'; 
  last_name2 VARCHAR2(10) := 'HTML5'; 
BEGIN 
  IF last_name1 > last_name2 THEN 
    DBMS_OUTPUT.PUT_LINE (last_name1 || ' is greater than ' || last_name2); 
  ELSE 
    DBMS_OUTPUT.PUT_LINE (last_name2 || ' is greater than ' || last_name1 ); 
  END IF; 
END; 
/

The code above generates the following result.

Note

If both values are CHAR, PL/SQL blank-pads the shorter value to the length of the longer value before comparing them.

If either value is VARCHAR2, PL/SQL does not adjust their lengths before comparing them.


DECLARE --   w ww .j  a v a  2s . c om
  last_name1 CHAR(5)  := 'HELLO';     -- no trailing blanks 
  last_name2 CHAR(10) := 'HELLO   ';  -- trailing blanks 
BEGIN 
 IF last_name1 = last_name2 THEN 
   DBMS_OUTPUT.PUT_LINE (last_name1 || ' is equal to ' || last_name2); 
 ELSE 
   DBMS_OUTPUT.PUT_LINE (last_name2 || ' is not equal to ' || last_name1); 
 END IF; 
END; 
/

The code above generates the following result.

Comparing Two VARCHAR2 Values


DECLARE --   w  w w . j  a v a2 s  . c o  m
  last_name1 VARCHAR2(10) := 'HELLO';     -- no trailing blanks 
  last_name2 VARCHAR2(10) := 'HELLO   ';  -- trailing blanks 
BEGIN 
  IF last_name1 = last_name2 THEN 
    DBMS_OUTPUT.PUT_LINE (last_name1 || ' is equal to ' || last_name2 ); 
  ELSE 
    DBMS_OUTPUT.PUT_LINE (last_name2 || ' is not equal to ' || last_name1); 
  END IF; 
END; 
/

The code above generates the following result.

Example 2

Comparing CHAR Value and VARCHAR2 Value


DECLARE --  ww  w  . j a va2  s. c o  m
  last_name1 VARCHAR2(10) := 'HELLO'; 
  last_name2 CHAR(10)     := 'HELLO';  -- PL/SQL blank-pads value 
BEGIN 
  IF last_name1 = last_name2 THEN 
    DBMS_OUTPUT.PUT_LINE (last_name1 || ' is equal to ' || last_name2); 
  ELSE 
    DBMS_OUTPUT.PUT_LINE (last_name2 || ' is not equal to ' || last_name1 ); 
  END IF; 
END; 
/

The code above generates the following result.