PL/SQL - Strings

Strings

Fixed-length strings
Variable-length strings
Character large objects (CLOBs)

Declaring String Variables

DECLARE
   v_name varchar2(20);
   v_company varchar2(30);
   v_introduction clob;
   v_choice char(1);
BEGIN
   v_name := 'Mr. Hareesh';
   v_company := 'H Group';
   v_introduction := ' Hello! I''m Hareesh Pothuguntla from H Group.';
   v_choice := 'y';
   IF choice = 'y' THEN
      dbms_output.put_line(v_name);
      dbms_output.put_line(v_company);
      dbms_output.put_line(v_introduction);
   END IF;
END;
/

If you want to declare a fixed-length string, use the CHAR datatype.

v_flag CHAR(1) := 'Y';
v_flag CHAR    := 'Y';

String Functions and Operators

The concatenation operator (||) is used for joining two strings. String functions are,

1. ASCII(x);
2. CHR(x);
3. CONCAT(x, y);
4. INITCAP(x);
5. INSTR(x, find_string [, start] [, occurrence]);
6. INSTRB(x); 
7. LENGTH(x); 
8. LENGTHB(x); 
9. LOWER(x); 
10. LPAD(x, width [, pad_string]) ; 
11. LTRIM(x [, trim_string]); 
12. NANVL(x, value); 
13. NLS_INITCAP(x); 
14. NLS_LOWER(x) ; 
15. NLS_UPPER(x); 
16. NLSSORT(x); 
17. NVL(x, value); 
18. NVL2(x, value1, value2); 
19. REPLACE(x, search_string, replace_string); 
20. RPAD(x, width [, pad_string]); 
21. RTRIM(x [, trim_string]); 
22. SOUNDEX(x) ; 
23. SUBSTR(x, start [, length]); 
24. SUBSTRB(x); 
25. TRIM([trim_char FROM) x); 
26. UPPER(x); 

Example 1

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

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