String Functions and Operators

The concatenation operator (||) is used for joining two strings.

String functions are:

• ASCII(x);
• CHR(x);
• CONCAT(x, y);
• INITCAP(x);
• INSTR(x, find_string [, start] [, occurrence]);
• INSTRB(x); 
• LENGTH(x); 
• LENGTHB(x); 
• LOWER(x); 
• LPAD(x, width [, pad_string]) ; 
• LTRIM(x [, trim_string]); 
• NANVL(x, value); 
• NLS_INITCAP(x); 
• NLS_LOWER(x) ; 
• NLS_UPPER(x); 
• NLSSORT(x); 
• NVL(x, value); 
• NVL2(x, value1, value2); 
• REPLACE(x, search_string, replace_string); 
• RPAD(x, width [, pad_string]); 
• RTRIM(x [, trim_string]); 
• SOUNDEX(x) ; 
• SUBSTR(x, start [, length]); 
• SUBSTRB(x); 
• TRIM([trim_char FROM) x); 
• UPPER(x);

 Sample code:

DECLARE

v_company_name varchar2(11) := 'Ask Haressh Technical Blog';

BEGIN

dbms_output.put_line(UPPER(v_company_name)); -- Convert string to upper case

dbms_output.put_line(LOWER(v_company_name)); -- Convert string to lower case

dbms_output.put_line(INITCAP(v_company_name)); -- Convert string as First letter as Upper and remaining as lower case

dbms_output.put_line ( SUBSTR (v_company_name, 1, 1)); -- Get the first letter from string

dbms_output.put_line ( SUBSTR (v_company_name, -1, 1)); -- Get the last letter from string

/* retrieve the five characters, 

starting from the seventh position. */

dbms_output.put_line ( SUBSTR (v_company_name, 7, 5));

/* retrieve the remaining of the string,

starting from the second position. */

dbms_output.put_line ( SUBSTR (v_company_name, 2));

/* find the location of the first "e" */

dbms_output.put_line ( INSTR (v_company_name, 'e'));

END;

Sample Code:

DECLARE

v_company_name varchar2(30) := '......Ask Hareesh.....';

BEGIN

dbms_output.put_line(RTRIM(v_company_name,'.'));

dbms_output.put_line(LTRIM(v_company_name, '.'));

dbms_output.put_line(TRIM( '.' from v_company_name));

END;

Complete Tutorial