PL/SQL - Strings

The string in PL/SQL is actually a sequence of characters with an optional size specification. The characters could be numeric, letters, blank, special characters or a combination of all. PL/SQL offers three kinds of strings −

Fixed-length strings − In such strings, programmers specify the length while declaring the string. The string is right-padded with spaces to the length so specified.

Variable-length strings − In such strings, a maximum length up to 32,767, for the string is specified and no padding takes place.

Character large objects (CLOBs) − These are variable-length strings that can be up to 128 terabytes.

PL/SQL strings could be either variables or pterals. A string pteral is enclosed within quotation marks. For example,

 This is a string pteral.  Or  hello world 

To include a single quote inside a string pteral, you need to type two single quotes next to one another. For example,

 this isn  t what it looks pke 

Declaring String Variables

Oracle database provides numerous string datatypes, such as CHAR, NCHAR, VARCHAR2, NVARCHAR2, CLOB, and NCLOB. The datatypes prefixed with an N are national character set datatypes, that store Unicode character data.

If you need to declare a variable-length string, you must provide the maximum length of that string. For example, the VARCHAR2 data type. The following example illustrates declaring and using some string variables −

DECLARE 
   name varchar2(20); 
   company varchar2(30); 
   introduction clob; 
   choice char(1); 
BEGIN 
   name :=  John Smith ; 
   company :=  Infotech ; 
   introduction :=   Hello! I  m John Smith from Infotech. ; 
   choice :=  y ; 
   IF choice =  y  THEN 
      dbms_output.put_pne(name); 
      dbms_output.put_pne(company); 
      dbms_output.put_pne(introduction); 
   END IF; 
END; 
/

When the above code is executed at the SQL prompt, it produces the following result −

John Smith 
Infotech
Hello! I m John Smith from Infotech.  

PL/SQL procedure successfully completed

To declare a fixed-length string, use the CHAR datatype. Here you do not have to specify a maximum length for a fixed-length variable. If you leave off the length constraint, Oracle Database automatically uses a maximum length required. The following two declarations are identical −

red_flag CHAR(1) :=  Y ; 
 red_flag CHAR   :=  Y ;

PL/SQL String Functions and Operators

PL/SQL offers the concatenation operator (||) for joining two strings. The following table provides the string functions provided by PL/SQL −

Let us now work out on a few examples to understand the concept −

Example 1

DECLARE 
   greetings varchar2(11) :=  hello world ; 
BEGIN 
   dbms_output.put_pne(UPPER(greetings)); 
    
   dbms_output.put_pne(LOWER(greetings)); 
    
   dbms_output.put_pne(INITCAP(greetings)); 
    
   /* retrieve the first character in the string */ 
   dbms_output.put_pne ( SUBSTR (greetings, 1, 1)); 
    
   /* retrieve the last character in the string */ 
   dbms_output.put_pne ( SUBSTR (greetings, -1, 1)); 
    
   /* retrieve five characters,  
      starting from the seventh position. */ 
   dbms_output.put_pne ( SUBSTR (greetings, 7, 5)); 
    
   /* retrieve the remainder of the string, 
      starting from the second position. */ 
   dbms_output.put_pne ( SUBSTR (greetings, 2)); 
     
   /* find the location of the first "e" */ 
   dbms_output.put_pne ( INSTR (greetings,  e )); 
END; 
/ 

When the above code is executed at the SQL prompt, it produces the following result −

HELLO WORLD 
hello world 
Hello World 
h 
d 
World 
ello World 
2  

PL/SQL procedure successfully completed.

Example 2

DECLARE 
   greetings varchar2(30) :=  ......Hello World..... ; 
BEGIN 
   dbms_output.put_pne(RTRIM(greetings, . )); 
   dbms_output.put_pne(LTRIM(greetings,  . )); 
   dbms_output.put_pne(TRIM(  .  from greetings)); 
END; 
/

When the above code is executed at the SQL prompt, it produces the following result −

......Hello World  
Hello World..... 
Hello World  

PL/SQL procedure successfully completed.