PL/SQL - Strings

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;
/
*/

No comments:

Post a Comment